«

Hi All,

I have a series of large CSV files (the largest is several GB) that I
need to load into SQL. To perform the load I'm hoping to use the SQL
command BULK INSERT. But I have a problem, I have lines in the CSV file
like the second one here:

12,some text,34
56,"some text, with a comma in",78

The BULK INSERT command fails to understand the significance of the
quotation marks and treats the second comma in the second line as a
delimiter.

Thus I would like to replace each occurrence of a comma in the files,
when and only when used as a separator, with a different separator ("/"
for example). Does anyone have a Perl script they can share with me
that will do this, i.e. that will find and replace commas which are not
enclosed in text within quote marks?

Cheers,

Tim.

wrote:
> Thus I would like to replace each occurrence of a comma in the files...

You might benefit from a recent thread in this group:

http://tinyurl.com/9ut7w

The thread discussed an aspect of Damian Conway's (outstanding) book,
"Perl Best Practices," but it just happens to also address your
question.

schreef:

> The BULK INSERT command fails to understand the significance of the
> quotation marks and treats the second comma in the second line as a
> delimiter.

Which SQL-environment?

http://dev.mysql.com/doc/refman/5.0/en/load-data.html

I prefer Tab as the field delimiter.

--
Affijn, Ruud

"Gewoon is een tijger."

"" <> wrote:
> Hi All,
>
> I have a series of large CSV files (the largest is several GB) that I
> need to load into SQL.

SQL is a language. You cannot insert data into a language. What is the
SQL-processing database server you are trying to use?


> To perform the load I'm hoping to use the SQL
> command BULK INSERT. But I have a problem, I have lines in the CSV file
> like the second one here:
>
> 12,some text,34
> 56,"some text, with a comma in",78
>
> The BULK INSERT command fails to understand the significance of the
> quotation marks and treats the second comma in the second line as a
> delimiter.

I don't know "BULK INSERT", but I do know Oracles sqlldr and MySQL's
"LOAD DATA" or mysqlimport. Both of them allow you specify a "Fields
enclosed by" character, such they can take your large data files just as
they are. I would be very surprised if "BULK INSERT" is not similarly
configurable.

> Thus I would like to replace each occurrence of a comma in the files,
> when and only when used as a separator, with a different separator ("/"
> for example). Does anyone have a Perl script they can share with me
> that will do this, i.e. that will find and replace commas which are not
> enclosed in text within quote marks?

Text::CSV_XS

Xho

--
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service $9.95/Month 30GB

<> wrote:


> i.e. that will find and replace commas which are not
> enclosed in text within quote marks?


Your Question is Asked Frequently, though it's not as easy to
find as it ought to be:

How can I split a [character] delimited string except when inside
[character]?


--
Tad McClellan SGML consulting
Perl programming
Fort Worth, Texas

Hi All,

Thanks all for your help, that certainly gives me scripts to try, a
library to pick over, new search terms to try, and I've ordered the
recommended book by Conway.

Xho asked:
>>> I don't know "BULK INSERT", but I do know Oracles sqlldr and MySQL's "LOAD DATA" or mysqlimport. Both of them allow you specify a "Fields enclosed by" character, such they can take your large data files just as they are. I would be very surprised if "BULK INSERT" is not similarly configurable <<<

I'm using Microsoft's SQL Server 2005. It has a tool for doing this
called SSIS, but because of problems with my installation I wanted to
get back to basics and do it in SQL using BULK INSERT. Unfortunately it
looks like the tantalisingly useful "fields enclosed by" character that
Xho refers to is not configurable:
http://msdn.microsoft.com/library/de...ba-bz_4fec.asp
( http://tinyurl.com/55ysl )

Cheers,

Tim.