• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

Insert Excel information to Access Table, cf error

Community Beginner ,
Dec 29, 2010 Dec 29, 2010

Copy link to clipboard

Copied

I am trying to create a file upload that will allow me to use a .csv file, to insert the data into an access database. I have the code written, but right now, it's giving me an error and I can't figure out what I'm doing wrong, this is my first time doing this, so any and all help would be greatly appreciated.

This is my code to read the .csv file.:


<cftry>
<cffile action="DELETE" file="#FORM.attachment_1#"/>
<cfcatch>
<!--- File delete error. --->
</cfcatch>
</cftry>
<cfelse>
<!--- no errors with the file upload so lets upload it--->

<cffile action="upload"
                 filefield="attachment_1"
                 result="myResult"
                 accept = ""
                 destination="f:\websites\211562Fe3\uploads\"
                 nameconflict="Makeunique">
                
<cfset svrFile = "#myResult.ServerDirectory#"&"\"&"#myResult.ServerFile#"/>

<!--- get and read the CSV-TXT file --->
<cffile action="read" file="#svrFile#" variable="csvfile">

<!--- loop through the CSV-TXT file on line breaks and insert into database --->
<cfloop index="index" list="#csvfile#" delimiters="#chr(10)##chr(13)#">
    <cfquery name="importcsv" datasource="#APPLICATION.dataSource#">
         INSERT INTO employees (siteID,empstatus,empfirstname,empmiddlename,emplastname,empnickname,emplocation,empgender,empdob,empdoh,empee)
         VALUES
                  ('#listgetAt('#index#',1, ',')#',
                   '#listgetAt('#index#',2, ',')#',
                   '#listgetAt('#index#',3, ',')#',
                   '#listgetAt('#index#',4, ',')#',
                   '#listgetAt('#index#',5, ',')#',
                   '#listgetAt('#index#',6, ',')#',
                   '#listgetAt('#index#',7, ',')#',
                   '#listgetAt('#index#',8, ',')#',
                   '#listgetAt('#index#',9, ',')#',
                   '#listgetAt('#index#',10, ',')#',
                   '#listgetAt('#index#',11, ',')#'
                  )
   </cfquery>
</cfloop>

<cffile action="DELETE" file="#svrFile#"/>


<!--- use a simple database query to check the results of the import - dumping query to screen --->
<cfquery name="rscsvdemo" datasource="#APPLICATION.dataSource#">
         SELECT * FROM csvdemo
</cfquery>
<cfdump var="#employees#">
</cfif>

The error is in the insert query, I have all my columns named the same ad the database table and in the same order, I also formatted each cell in the .csv to be the same as the database.


This is the error:

Error Executing Database Query.

[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC  Microsoft Access Driver] Data type mismatch in criteria expression.

The error occurred in  C:\websites\211562fe3\partners\cfm\csvUploadAction.cfm: line 60

58 :                    '#listgetAt('#index#',9, ',')#',

59 :                    '#listgetAt('#index#',10, ',')#',

60 :                    '#listgetAt('#index#',11, ',')#' 

61 :                   )

62 :    </cfquery>

Thank you

TOPICS
Advanced techniques

Views

2.8K

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Dec 29, 2010 Dec 29, 2010

Copy link to clipboard

Copied

Access is pretty forgiving about such things, but by putting quotes around all your values, you're saying they're all strings. I suspect the ID is numeric, and there's a coupla dates in there too. These should be passed as the correct data type.

Also, you should be using tags when passing dynamic values to the DB, rather than hard-coding them in your SQL string.

And - more trivially but definitely a "best practice" thing - learn when & when not to use pound signs. More to the point, you are quoting a lot of your variables for no good reason, which is then forcing you to resolve them with the pound-signs. Only use quotes and pound-signs when it's actually necessary.

--

Adam

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Beginner ,
Dec 29, 2010 Dec 29, 2010

Copy link to clipboard

Copied

how owuld I make tags? cfparam tags? Can you write one? and then I just make it like a normal insert query with cfqueryparam from the "tags" correct?

Also, besides the query, where am I quoting and pounding too much?

Thank you for your help, like I said, this is the first time I am trying to make excel update access.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Dec 29, 2010 Dec 29, 2010

Copy link to clipboard

Copied

Argh, sorry: the forums software munged my reply. Yes, I meant (and typed...) CFQUERYPARAM tags.

As for the quotes / pound-signs, every time you have this:

"#someVar#"

in a CF expression, you can simple do this:

someVar

Eg:

someOtherVar = myVar;

rather than:

someOtherVar = "#myVar#";

--

Adam

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Beginner ,
Dec 29, 2010 Dec 29, 2010

Copy link to clipboard

Copied

I changed my code a little.. now I get an error about converting a date.. so I must be on the right track.... Can you help me straiten this out?

This is my "new" code:

<cftry>
<cffile action="DELETE" file="#FORM.attachment_1#"/>
<cfcatch>
<!--- File delete error. --->
</cfcatch>
</cftry>
<cfelse>
<!--- no errors with the file upload so lets upload it--->

<cffile action="upload"
                 filefield="attachment_1"
                 result="myResult"
                 accept = ""
                 destination="c:\websites\211562Fe3\uploads\"
                 nameconflict="Makeunique">
                
<cfset svrFile = "#myResult.ServerDirectory#"&"\"&"#myResult.ServerFile#"/>

<!--- get and read the CSV-TXT file --->
<cffile action="read" file="#svrFile#" variable="csvfile">

<!--- loop through the CSV-TXT file on line breaks and insert into database --->
<cfloop index="i" list="#csvfile#" delimiters="#chr(10)##chr(13)#">
    <cfquery name="importcsv" datasource="#APPLICATION.dataSource#">
         INSERT INTO employees (siteID,empstatus,empfirstname,empmiddlename,emplastname,empnickname,emplocation,empgender,empdob,empdoh,empee)
         VALUES
                  (<CFQUERYPARAM CFSQLTYPE="cf_sql_varchar" VALUE="#trim(listgetAt(i,1))#">,
                    <CFQUERYPARAM CFSQLTYPE="cf_sql_varchar" VALUE="#listgetAt(i,2)#">,
                    <CFQUERYPARAM CFSQLTYPE="cf_sql_varchar" VALUE="#listgetAt(i,3)#">,
                 <CFQUERYPARAM CFSQLTYPE="cf_sql_varchar" VALUE="#trim(listgetAt(i,4))#">,
                 <CFQUERYPARAM CFSQLTYPE="cf_sql_varchar" VALUE="#listgetAt(i,5)#">,
                 <CFQUERYPARAM CFSQLTYPE="cf_sql_varchar" VALUE="#listgetAt(i,6)#">,
                 <CFQUERYPARAM CFSQLTYPE="cf_sql_varchar" VALUE="#listgetAt(i,7)#">,
                 <CFQUERYPARAM CFSQLTYPE="cf_sql_varchar" VALUE="#listgetAt(i,8)#">,
                 <CFQUERYPARAM CFSQLTYPE="CF_SQL_DATE" VALUE="#listgetAt(i,9)#">,
                 <CFQUERYPARAM CFSQLTYPE="CF_SQL_DATE" VALUE="#listgetAt(i,9)#">,
                 <CFQUERYPARAM CFSQLTYPE="cf_sql_varchar" VALUE="#listgetAt(i,9)#">
                  )
   </cfquery>
</cfloop>

<cffile action="DELETE" file="#svrFile#"/>


<!--- use a simple database query to check the results of the import - dumping query to screen --->
<cfquery name="rscsvdemo" datasource="#APPLICATION.dataSource#">
         SELECT * FROM employees
</cfquery>
<cfdump var="#employees#">
</cfif>

I'm not sure it's totally correct.. but the error is different!

This is the error:

The cause of this output exception was that:  coldfusion.runtime.Cast$DateStringConversionException: The value empdob  cannot be converted to a date..

The error occurred inf :\websites\211562fe3\partners\cfm\csvUploadAction.cfm: line 58

56 :                  <CFQUERYPARAM CFSQLTYPE="cf_sql_varchar"  VALUE="#listgetAt(i,7)#">,

57 :                  <CFQUERYPARAM CFSQLTYPE="cf_sql_varchar"  VALUE="#listgetAt(i,8)#">,

58 :                  <CFQUERYPARAM CFSQLTYPE="CF_SQL_DATE"  VALUE="#listgetAt(i,9)#">,

59 :                  <CFQUERYPARAM CFSQLTYPE="CF_SQL_DATE"  VALUE="#listgetAt(i,9)#">,

60 :                  <CFQUERYPARAM CFSQLTYPE="cf_sql_varchar"  VALUE="#listgetAt(i,9)#">

Also I can make my .csv file available for you if you need it. Also, the date is being inseted in this format: 3/3/1952

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Dec 29, 2010 Dec 29, 2010

Copy link to clipboard

Copied

When the user gives you a string that is supposed to represent a date, parsedatetime() will convert it to a data object.  Date objects are better than string in many situations, such as variables in queries.

Just out of curiousity, if you open your csv file with notepad, is each field surrounded by double quotes?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Beginner ,
Dec 29, 2010 Dec 29, 2010

Copy link to clipboard

Copied

ok, so how do I write a cfset to convert the excel date to one the database will accept.

Also, no, each field is not surrounded by quotes... How do I fix that?

This is what I see in wordpad:
siteID,empstatus,empfirstname,empmiddlename,emplastname,empnickname,emplocation,empgender,empdob,empdoh,empee
12,active,Douglas,L,Berg,,"Marshall, MN",Male,3/3/1952,,

Thanks again

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Dec 29, 2010 Dec 29, 2010

Copy link to clipboard

Copied

Regarding "so how do I write a cfset to convert the excel date to one the database will accept"

I gave you the name of the function.  If you need help with it, google "coldfusion name of function" and the reference material you need will come up.

Regarding the rest of your post, the reason for the error is that the person does not have a nickname.  This is creating empty list elements which Coldfusion tends to ignore.  You are going to have to deal with this eventually, so now is as good a time as any.

Your task might be simpler if you use cfhttp to read your file instead of cffile.  The documentation will tell you how you can get a query object which should give you blank fields instead of empty list elements.

Even if you stick with cffile, you still have to decide what to do about missing data and then write the applicable code.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Beginner ,
Dec 29, 2010 Dec 29, 2010

Copy link to clipboard

Copied

I'm researching the functions you are pointing me too.. now I'm trying to write the code, one step at a time...

Right now, I'm uploading the file, and using cfhttp to read it, then dump those results.. I haven't gotten to inserting the info yet..

Right now, I'm getting an error with this code:

<cftry>
<cffile action="DELETE" file="#FORM.attachment_1#"/>
<cfcatch>
<!--- File delete error. --->
</cfcatch>
</cftry>
<cfelse>
<!--- no errors with the file upload so lets upload it--->.

<cffile action="upload"
                 filefield="attachment_1"
                 result="myResult"
                 accept = ""
                 destination="c:\websites\211562Fe3\uploads\"
                 nameconflict="Makeunique">
                
<cfset svrFile = "#myResult.ServerDirectory#"&"\"&"#myResult.ServerFile#"/>


<cfset dataUrl = ("http://" &cgi.server_name & getDirectoryFromPath( cgi.script_name ) & "svrFile") />


<cfhttp name="employeeCSV" method="get" url="#dataUrl#" />

<cfdump var="#employeeCSV#" label="CSV Query" />

At this point, it's simple, upload it, read it, but it's giving me this error:

The column name is invalid.
Column names must be valid variable names. They must start with a letter and can only include letters, numbers, and underscores.

The error occurred in C:\websites\211562fe3\partners\cfm\csvUploadAction.cfm: line 42

40 :
41 : <cfset dataUrl = ("http://" &cgi.server_name & getDirectoryFromPath( cgi.script_name ) & "svrFile") />
42 : <cfhttp name="employeeCSV" method="get" url="#dataUrl#" />
43 :
44 : <cfdump var="#employeeCSV#" label="CSV Query" />

Now all this aside, once I get this working, what do I do to grab the info from the .csv on the query insert: use something like this?

<CFQUERYPARAM CFSQLTYPE="cf_sql_varchar" VALUE="#trim(employeeCSV.siteID)#">,

and all the other columns that are labled in the .csv doc go after this?

I'm finding a lot of docs on this, but not exactly what I need.. so I'm trying to figure out how to make this work for what I need, and your idea seems to be better.. if i can get over these little issues. Thank you.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Dec 29, 2010 Dec 29, 2010

Copy link to clipboard

Copied

At first glance it appears that you are attempting to open a different file than the one you uploaded.  It's hard to tell because you are using so many functions.

You might want to create a mapping for your uploads directory, something like /uploads.  Then your code becomes this

<cfhttp method="get" url="/uploads/#Cffile.clientFile#" name="NameOfYourQuery"></cfhttp>

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Beginner ,
Dec 29, 2010 Dec 29, 2010

Copy link to clipboard

Copied

I can clean this up in a bit, it is reading the file, now it's telling me there isn't the proper amount of columns and to verify it...

This is what I've done so far:

<cftry>
<cffile action="DELETE" file="#FORM.attachment_1#"/>
<cfcatch>
<!--- File delete error. --->
</cfcatch>
</cftry>
<cfelse>
<!--- no errors with the file upload so lets upload it--->

<cffile action="upload"
                 destination="c:\websites\211562Fe3\uploads\"
                 filefield="#FORM.attachment_1#"
                 nameconflict="overwrite"
                 result="myResult">
                
<cfset svrFile = "#myResult.ServerDirectory#"&"\"&"#myResult.ServerFile#"/>
<cfset dataUrl = ("http://" &cgi.server_name & getDirectoryFromPath( cgi.script_name ) & "svrFile") />
<cfhttp url="#dataUrl#" name="employee" columns="siteID,empstatus,empfirstname,empmiddlename,emplastname,empnickname,emplocation,empgender,empdob,empdoh,empee" delimiter="#chr(32)#" resolveurl="no" />

<cfdump var="#employee#" label="CSV Query" />

This is the error now:


Incorrect number of columns in row.
Verify the number of columns specified in the columns attribute and in the target file

The error occurred in C:\websites\211562fe3\partners\cfm\csvUploadAction.cfm: line 40

38 : <cfset svrFile = "#myResult.ServerDirectory#"&"\"&"#myResult.ServerFile#"/>
39 : <cfset dataUrl = ("http://" &cgi.server_name & getDirectoryFromPath( cgi.script_name ) & "svrFile") />
40 : <cfhttp url="#dataUrl#" name="employee" columns="siteID,empstatus,empfirstname,empmiddlename,emplastname,empnickname,emplocation,empgender,empdob,empdoh,empee" delimiter="#chr(32)#" resolveurl="no" />
41 :
42 : <cfdump var="#employee#" label="CSV Query" />


Ok.. now what! The columns named in the columns portion of the tag are the columns in the .csv file.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Dec 29, 2010 Dec 29, 2010

Copy link to clipboard

Copied

The error message was pretty explicit. Maybe cfhttp doesn't like empty cells either.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Beginner ,
Dec 29, 2010 Dec 29, 2010

Copy link to clipboard

Copied

I put some text in all fields for this, to make sure it first accepts all fiels filled, and it's giving this error.

How about a simple question

for using the parsdate would i use it like this?<CFQUERYPARAM CFSQLTYPE="CF_SQL_DATE" VALUE="#ParseDateTime(listgetAt(i,9))#">

I don't need time though. Can I just use parsedate?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Dec 29, 2010 Dec 29, 2010

Copy link to clipboard

Copied

Regarding using parsedate(), try it and see what happens.

You didn't say what error message you were getting.  Is it still the one for the wrong number of columns?

I was curious about using cfhttp when there were empty cells.  I created a file that looks like this:

abcd
1234
4578

Then I ran this code:

<cfhttp method="get" url="/work/abc.csv" name="x">
</cfhttp>

<cfdump var="#x#" metainfo=no>

and got my query as expected.  Then I took out a couple of numbers, and ran the same code.  I got my query with empty fields.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Beginner ,
Dec 29, 2010 Dec 29, 2010

Copy link to clipboard

Copied

ok, then my .csv file is formatted improperly. I was using excel converted to a .csv what am I doing wrong, because it isn't coming out like you posted. a1, b1 and so on is the titles of each column, but they are not separated like you have it... why?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Dec 30, 2010 Dec 30, 2010

Copy link to clipboard

Copied

All I can tell you is what I did.  I opened excel, typed letters into the cells A1 to D1, numbers into A2 to D3, and saved it as csv.  In Notepad, it now looks like this:

a,b,c,d
1,2,3,4
4,,7,8

What you saw in my earlier post was pasting from excel.  This rich textarea converted it to an html table.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Dec 30, 2010 Dec 30, 2010

Copy link to clipboard

Copied

LATEST

OK: a tip? When working with text files, don't open then in an Office application as the app will do all sorts of things to try to present the file all "nice" for the user. However what Excel - in this case - displays is not necessarily what's in the file. And it's what's in the file that matters.

By all means •create• the file with Excel, but when troubleshooting problems, look at the actual file contents, not what Excel suggests the file might look like.

Use Notepad or something. Use whatever you write your CFML code in! That's probably the most obvious option.

--

Adam

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Dec 30, 2010 Dec 30, 2010

Copy link to clipboard

Copied

That said, if the "date" value comes from user input or any system that could be "unreliable", make sure to check the string •can• be parsed as a date first, and try/catch the parseDateTime() call.

--

Adam

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation