Re: Importing from CSV to a database

2010-10-22 Thread Will Tomlinson 

Thanks, but it is not that simple.
I have plenty of things to check, convert, validate, etc.
Don't forget an Excel file is created and filled by humans ;-)

I would figure out a way to use my RDBMS to handle this. Navicat or SQL Server 
tools to do it. 

Then clean the data or whatever once it's in there. To reference those crappy 
fieldnames you'll need to use aliases

SELECT some[space]crap[space] fieldname as somethingICanUse
FROM someTable 

~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:338486
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

Re: Importing from CSV to a database

2010-10-22 Thread M P 

I agree using something like Navicat to load the data would be a much better 
solution, but if you must use code, then I would parse those table names and 
replace all of the bad characters and spaces before creating the tables.

Special characters and spaces just cause too many problems to even try to use 
in table names. The only non-alphanumeric character I EVER use in a table name 
is an underscore.

Is there some reason you can't rename the tables when they're created to strip 
away the offensive characters?



~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:338488
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

Re: Importing from CSV to a database

2010-10-21 Thread Ketan Jetty 

Another way to do this is read the file, loop thru the lines and massage the 
data as wanted when inserting into the database. This way you can have better 
control on the data manipulation.

cfloop file=#expandPath('.')#\sampledata.csv from=1 index=line
 cfquery name=qi datasource=#mydsn#
  INSERT INTO tableONE
  (
   col1
   ,col2
   ,col3
   ,col4
  )
  VALUES
  (
'#ListGetAt(line,1)#'
   ,'#ListGetAt(line,2)#'
   ,'#ListGetAt(line,3)#'
   ,'#ListGetAt(line,4)#'
  )
 /cfquery
/cfloop

Thanks,

Ketan Jetty
http://KetanJetty.com


~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:338447
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

Re: Importing from CSV to a database

2010-10-21 Thread Casey Dougall 

On Thu, Oct 21, 2010 at 1:03 PM, Ketan Jetty kje...@yahoo.com wrote:


 Another way to do this is read the file, loop thru the lines and massage
 the data as wanted when inserting into the database. This way you can have
 better control on the data manipulation.

 cfloop file=#expandPath('.')#\sampledata.csv from=1 index=line
  cfquery name=qi datasource=#mydsn#
  INSERT INTO tableONE
  (
   col1
   ,col2
   ,col3
   ,col4
  )
  VALUES
  (
'#ListGetAt(line,1)#'
   ,'#ListGetAt(line,2)#'
   ,'#ListGetAt(line,3)#'
   ,'#ListGetAt(line,4)#'
  )
  /cfquery
 /cfloop

 Thanks,

 Ketan Jetty
 http://KetanJetty.com



If this is MS SQL you can do this as well.

Right click the Database, select tasks, import data, select Excel as source
of data, then the DB and table you are inserting it to, there is options to
align the colums with the column names in the table, then run.


~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:338449
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

Re: Importing from CSV to a database

2010-10-21 Thread Claude Schnéegans 

 If this is MS SQL you can do this as well.

Thanks, but it is not that simple.
I have plenty of things to check, convert, validate, etc.
Don't forget an Excel file is created and filled by humans ;-)

~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:338464
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

Re: Importing from CSV to a database

2010-10-20 Thread Leigh 

 I've tried getMembers['Région administrative'] but it
 causes an error.

Did you forget the row number or is that typo? 

ie
queryName[column name][ rowNumber ] 


  

~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:338387
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

Re: Importing from CSV to a database

2010-10-20 Thread Claude Schnéegans 

 Did you forget the row number or is that typo?

Actually, I'm in a loop on the query, I thought the row number would be 
implicit as usual.
But you're right, queryName[column name][queryName.currentRow] does work.
This is kind of weird since queryName.columnName works, bur not 
queryName[columnName]

Thanks anyway, you solved my problem.


~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:338403
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm

Re: Importing from CSV to a database

2010-10-20 Thread Leigh 

 This is kind of weird since queryName.columnName works, bur
 not queryName[columnName]

I guess the difference is queryName[columnName] gives you access to the 
column array. I thought it was strange at first too. But realized it is a good 
thing, since it allows you to access values in any row with array notation. 
Just change the row index.

-Leigh 


  

~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive: 
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:338409
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm