cffile and inserting tab-delimited data into Access database

Dev Shed Forums Sponsor:

July 26th, 2005, 11:01 PM

midimidi

Contributing User

Join Date: Feb 2005

Posts: 83

Time spent in forums: 16 h 29 m
Reputation Power: 4

cffile and inserting tab-delimited data into Access database

I have a text file with data in tab-delimited form - for simplicity, only two rows of data. Each row has five elements. On the database side, I have an access table with a counter and five columns.

The following code gives me "Invalid list index 2 - In function ListGetAt(list, index [, delimiters]), the value of index, 2, is not a valid as the first argument (this list has 1 elements). Valid indexes are in the range 1 through the number of elements in the list."

I'm a bit confused how this works - why does the list have only one element? What I want is for each row to be entered as a row in the database.

Code:

<cffile action="read" file="#targetfile#" variable="fileData">

<cfloop index="record" list="#fileData#" delimiters="#chr(9)##chr(13)#">
  <cfquery name="qryInsert" datasource="month">
    INSERT INTO month_create_test(column4,column5)
	VALUES (
	  '#ListGetAt(record,1,chr(9))#','#ListGetAt(record,2,chr(9))#','#ListGetAt(record,3,chr(9))#','#ListG  etAt(record,4,chr(9))#','#ListGetAt(record,5,chr(9))#'
	  )
  </cfquery>
</cfloop>

This code confirms the single element problem and displays all data in the text file with single spaces in between, nothing repeated. Why?

Code:

<cffile action="read" file="#targetfile#" variable="fileData">

<cfloop index="record" list="#fileData#" delimiters="#chr(9)##chr(13)#">
<cfoutput>#ListGetAt(record,1,chr(9))#</cfoutput>
</cfloop>

July 27th, 2005, 12:15 AM

kiteless

Moderator

Join Date: Jun 2002

Location: Raleigh, NC

Posts: 3,742

Time spent in forums: 1 Week 4 Days 20 h 59 m 14 sec
Reputation Power: 62

I'm not sure what you are asking...maybe you need to consult an ASCII table like this one ? The code you posted would treat each tab and carriage return as a list element, so if you are not expecting that to be the delimiter you need to do some more research. I think what you really want is two nested loops, one that treats each carriage return as a list delimiter (which would give you a single row of data) and then another loop that treats each tab as a delimiter (which would give you each list element).

__________________
Ask if you have a question, but also help answer questions that you have knowledge of! Thanks, Brian.
How to Post a Question in the Forums

July 27th, 2005, 11:13 AM

midimidi

Contributing User

Join Date: Feb 2005

Posts: 83

Time spent in forums: 16 h 29 m
Reputation Power: 4

Quote:

Originally Posted by kiteless

I'm not sure what you are asking...

I want to read in a tab-delimited file and import each tabbed element as a column, and each linefeed as a row. I surfed around and came up with the code above based on someone that I thought was doing the same thing (but apparently not!)

Quote:

I think what you really want is two nested loops, one that treats each carriage return as a list delimiter (which would give you a single row of data) and then another loop that treats each tab as a delimiter (which would give you each list element).

OK - shouldn't the following work then? (it doesn't...same error as above) - i.e. just seeing if I can at least cfoutput this in the same way that I would import it into a table in a query:

Code:

<cffile action="read" file="#targetfile#" variable="fileData">
<cfloop index="lines" list="#fileData#" delimiters="#chr(13)#">
  <cfloop index="tabs" list="#fileData#" delimiters="#chr(9)#">
    <cfoutput>#ListGetAt(tabs,2,chr(9))#</cfoutput>
  </cfloop>
</cfloop>

Here's an odd thought...again, the text file I'm using has two lines of 5 elements each, with tabs in between. if I do <cfoutput>#ListLen(fileData,chr(13))#</cfoutput>, I get the correct length of 2. But if I do #ListLen(fileData,chr(9))#, I get a length of 9? Is that right? Shouldn't it be 10?

July 27th, 2005, 11:23 AM

kiteless

Moderator

Join Date: Jun 2002

Location: Raleigh, NC

Posts: 3,742

Time spent in forums: 1 Week 4 Days 20 h 59 m 14 sec
Reputation Power: 62

You're still not doing the looping correctly. Also, maybe naming the loops a bit differently will make it more clear:

<cffile action="read" file="#targetfile#" variable="fileData">
<cfloop index="thisRow" list="#fileData#" delimiters="#chr(13)#">
<cfloop index="thisRowElement" list="#thisRow#" delimiters="#chr(9)#">
<cfoutput>#thisRowElement#</cfoutput>
</cfloop>
</cfloop>

July 27th, 2005, 12:48 PM

midimidi

Contributing User

Join Date: Feb 2005

Posts: 83

Time spent in forums: 16 h 29 m
Reputation Power: 4

Thanks kiteless - that's what I needed!!

Now if I can only figure out inputting Dates and Times into an Access database. In Dreamweaver, the database columns for dates and times are listed as "DATETIME 19".

SO - if I have "06/27/2005" what is the correct sql formatting for a inputting from a cfquery to these datetime objects in an Access database?

Here's my final code BTW, which does work, but I'm getting errors on the date and time elements:

Code:

<cffile action="read" file="#targetfile#" variable="fileData"> 
  <cfloop index="thisRow" list="#fileData#" delimiters="#chr(13)#">
    <cfquery name="qryInput" datasource="month">
    INSERT INTO month_create_test
      (Date,Time,Duration,Episode,Series)
    VALUES(
      <cfloop index="thisRowElement" list="#thisRow#" delimiters="#chr(9)#">
        '#thisRowElement#'
        <cfif ListLast(thisRow,chr(9)) eq thisRowElement>
        <cfelse>,
        </cfif>
      </cfloop>)
    </cfquery> 	 
  </cfloop>

July 27th, 2005, 01:47 PM

kiteless

Moderator

Join Date: Jun 2002

Location: Raleigh, NC

Posts: 3,742

Time spent in forums: 1 Week 4 Days 20 h 59 m 14 sec
Reputation Power: 62

Try using the createODBCDate() or createODBCDateTime() function.

July 27th, 2005, 02:05 PM

midimidi

Contributing User

Join Date: Feb 2005

Posts: 83

Time spent in forums: 16 h 29 m
Reputation Power: 4

The following code:

Code:

<cfquery name="qryInput" datasource="month">
  INSERT INTO month_create_test (Time,Episode,Series)
  VALUES('#createODBCDateTime(now())#','text1','text2')
</cfquery>

Produces the following error:

Quote:

Error Executing Database Query.
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement.
INSERT INTO month_create_test (Time,Name1,Name2) VALUES('{ts '2005-07-27 14:02:29'}','text1','text2')

Same thing with createODBCDate(). (Shows up as '{d '2005-07-27'}')

Note - no fields are required. If I just put in Name1,Name2, it works fine.

July 27th, 2005, 02:28 PM

kiteless

Moderator

Join Date: Jun 2002

Location: Raleigh, NC

Posts: 3,742

Time spent in forums: 1 Week 4 Days 20 h 59 m 14 sec
Reputation Power: 62

Not sure, I don't use Access much. But you will just need to try a bunch of formats (have you tried something like '2/2/05'?)

July 27th, 2005, 02:43 PM

midimidi

Contributing User

Join Date: Feb 2005

Posts: 83

Time spent in forums: 16 h 29 m
Reputation Power: 4

Quote:

Originally Posted by kiteless

Not sure, I don't use Access much. But you will just need to try a bunch of formats (have you tried something like '2/2/05'?)

6/6/05
6/6/2005
06/06/05
06/6/2005

6-6-05
6-6-2005
06-06-05
06-06-2005

2005-06-06 00:00:00.000

...what else is there?

Here's the SQL I used to create the table...maybe that's it? (I actually originally put "date" for Date and "time" for Time, but just tried this again with the following instead - Access creates the same datetime column apparently in either case)...this code was snatched from the web in a comparison between SQL Server and Access, so it should be correct, but maybe I'm missing something?:

Code:

<cfquery name="qryCreate" datasource="month">
  CREATE TABLE month_create_test
    ([ID] counter,
     [Date] datetime,
     [Time] datetime,
     [Duration] datetime,
     [Name1] text,
     [Name1] text,
  CONSTRAINT [Index1] PRIMARY KEY ([ID]));
</cfquery>

#10

July 27th, 2005, 02:51 PM

kiteless

Moderator

Join Date: Jun 2002

Location: Raleigh, NC

Posts: 3,742

Time spent in forums: 1 Week 4 Days 20 h 59 m 14 sec
Reputation Power: 62

Try all the options and function calls with both quotes and no quotes.

#11

July 27th, 2005, 02:58 PM

midimidi

Contributing User

Join Date: Feb 2005

Posts: 83

Time spent in forums: 16 h 29 m
Reputation Power: 4

GOT IT!

It didn't like the Date and Time naming of columns. Naming them dtDate and dtTime made it all work.

I can't thank you enough for your help around here kiteless!!

#12

July 27th, 2005, 03:44 PM

kiteless

Moderator

Join Date: Jun 2002

Location: Raleigh, NC

Posts: 3,742

Time spent in forums: 1 Week 4 Days 20 h 59 m 14 sec
Reputation Power: 62

Sure. Yes you always have to watch out for reserved words and you almost never want to name anything a basic name like 'user' or 'time' or 'date' or 'role', etc.

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread: