Many times we have been tasked with working out a way for an ordinary user to "upload" data into a system. This task is fraught with peril because users are not well versed in the idea of "normalized" data. We've tried the Excel file approach. Excel gives the user so many neat options they can't resist adding formatting, charts, graphs and fiddling with the headers. Even with protected files, clever users find ways to make the data incomplete or unusable. We've also tried access. This is workable but it takes more setup and it requires extra expense on the part of the user (namely, they must have excel). Enter the famed low-tech "csv" or "comma separated value" file.
This file is not rocket science by any means. It is a way to represent tabular data in a "plain text" fashion using commas and quotation marks. The commas separate the fields and the quotation marks identify the values (where needed). When are the quotation marks needed? Well, they are particularly needed when a comma might creep into a value. For example:
"Joe","Smith","112 Test Street"Notice that Mr. Jones is a "Jones, Jr". Without the quotations marks as text qualifier that row of data would have 4 pieces of data instead of 3, breaking normalization.
"Sam","Jones, Jr.",114 test street"
So CSV is a common "lowest denominator" format we can use to get data into our system. In fact, we work with data feeds for financial and analyst data quite often and many of them provide their data in this format by default. So learning how to work with CSV is a good choice for upgrading your skills.
Coldfusion does not provide some easy way to read this data into an array or query. Working with it as a set of lists is ok, but how do you get around the "text qualifier" issue? Consider this post by Ben Nadel (who is no CF_slouch). His function reads a CSV string into an array. It's a good function, but long and involved. Another approach (one which I've used a few times) is to place the csv file at a URL and use CFHTTP to read it into a query object. There is nothing wrong with either of these approaches, but if you are on a windows box I've found an easier way.
You can use the MS text driver to create a proxy for your file. Here are the steps.
Finally, you might note that this entry is similar to a popular technique I wrote about in this post on Connection DSN in CFMX. In the case of CSV files I suspect it will make life a lot easier.
Jalpino - I haven't had the chance to dig up the docs for it... I'm sure there is at least a subset of stuff you can do, but given the propensity of CSV files to be full of lousy data I would hesitate :).
"select top 1000 * from [" + filetable + "] WHERE F1 NOT EXIST(SELECT TOP ((10-1)*1000) F1 FROM [" + filetable + "] ORDER BY F1 DESC)"
and getting this error
ERROR [42000] [Microsoft][ODBC Text Driver] Syntax error in query expression 'F1 NOT EXIST(SELECT TOP ((10-1)*1000) F1 FROM [myfile.csv] ORDER BY F1 DESC)'.
any help
thanks in advance
gg
- Both the ColdFusion ODBC Server and Agent services must be running.
- The first line *must* contain column names. If not, and your first line ends up with data like: Ryan,201 B st,"omaha, NE",68154 - ColdFusion will choke on the comma inside the quotes.
- When referencing the filename in your CFQUERY tag, do NOT use quotes around it (single or double). This will cause the "The Microsoft Jet database engine could not find the object" error I've seen mentioned on cf-talk.
I created the Data Source in User DSN and SystemDSN and pointed to the directory and file where the csv is located but, I keep getting "Unable to retrieve error message from backend ODBC Driver." when trying to set the DSN in CF admin.
It's worth noting that the ODBC controll panel is actually in your windows platform. Go to cpanel / admin tools/ data sources (ODBC) / Click on the System DSN tab, and then click add. Use a microsoft text reader driver, and name the datasoruce.
Then after that go into the coldfusion datasource choose odbc socket, and the one you made via windows should be in a dropdown in the ODBC socket setup screen.
Works fine for me now.
Peace
Thnaks.
http://msdn.microsoft.com/en-us/library/ms709353.a...
does anyone know if you can specify this stuff using a connection string or something in the query?
Any one have an idea how to get around this problem?
If you are using a text qualifier in your CSV file that might do it. If you are exporting the CSV from Excel try adding a single quote (') in front of the IP address in the Excel file (that' ids it as a text field) before you export. I've not had this specific problem though - so no guarantees :)
No database coming from a Linux application. Can not change anything on CSV side. It there a way to set-up a data source definition file?
Question, when I attempt to append a where clause to the query it returns 0 results. Is that because it's querying a CSV file or because I'm doing something wrong?
Ex.
SELCT *
FROM #filename#
WHERE fieldname = 'something'