Hello there,
I need to pass data from a CSV file to a database, so hence I thought of CSV->SQL.
In my CSV file, it has about 70 fields per row of data. And there are other issues.
I found someone else online having the same problem with similar data issues of their CSV file.
I will put the other persons example, but illustrates the problem that I have very well:
*******************************************************************************
http://stackoverflow.com/questions/782353/sql-server-bulk-insert-of-csv-file-with-inconsistent-quotes
Is it possible to BULK INSERT (SQL Server) a CSV file in which the fields are only OCCASSIONALLY surrounded by quotes? Specifically, quotes only surround those fields that contain a ",".
In other words, I have data that looks like this (the first row contain headers):
id, company, rep, employees
729216,INGRAM MICRO INC.,"Stuart, Becky",523
729235,"GREAT PLAINS ENERGY, INC.","Nelson, Beena",114
721177,GEORGE WESTON BAKERIES INC,"Hogan, Meg",253
Because the quotes aren't consistent, I can't use '","' as a delimiter, and I don't know how to create a format file that accounts for this.
I tried using ',' as a delimter and loading it into a temporary table where every column is a varchar, then using some kludgy processing to strip out the quotes, but that doesn't work either, because the fields that contain ',' are split into multiple columns.
*******************************************************************************
This is the type of problem I am having with fields such as this:
"Stuart, Becky".
I have some solutions, I could write a Groovy script to drop all quotes and then count the
field place of where Stuart and Becky are and concatenate them.
But then I run into another problem of how to construct the SQL
string.
For example I would plan to write an SQL INSERT statement in a Multiline string using the operator """,
and do variable interpolation with the fields.
But I consider my approach kind of long.
For example I don't want to write:
INSERT INTO VALUES( $fields{1}, $fields{2), $fields{3},$fields{4},$fields{5},.......,$fields{70});
Is there a way I can maybe write a for-loop to create this INSERT statement.
Because I don't want to write /hardcode 70 fields into my code.
SO that is my approach.
If somebody knows a totally different easier approach, please let me know.
Sincerely,
Paul