Import CSV file to SQL table

[Database Administrator]

Greetings,

How do I import data from CSV file (source.csv) to my MSSQL table
(dbo.target)?

Kindly advise.

Thanks
ascll

[Database Administrator]

Check out the following topics in the BOL:

BULK INSERT
bcp.exe

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau


"nelson" wrote in message
news:%23GYOu6VuHHA.2272@TK2MSFTNGP04.phx.gbl...
Greetings,

How do I import data from CSV file (source.csv) to my MSSQL table
(dbo.target)?

Kindly advise.

Thanks
ascll

[Database Administrator]

I have two csv files that I need import into MSSQL every 30 minutes. One csv
I am able to perform an INSERT INTO table (column1, column2, etc) SELECT
column1, column2, etc. FROM OPENROWSET('MSDASQL', 'Driver={Microsoft Text
Driver (*.txt; *.csv)};DBQ=C:\DataFiles;', 'SELECT * from Materials.csv'),
however the other file has two columns with many invalid dates such as
00/00/0000 or 04/11/0704 (the first is output by SAP and the other is
fat-fingered). I know I need to read the file, but I am stuck as to how to
parse through it and remove any bad dates. BULK COPY won't help me on this.

Mike

"Tom Moreau" wrote:

> Check out the following topics in the BOL:
>
> BULK INSERT
> bcp.exe
>
> --
> Tom
>
> ----------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
>
> "nelson" wrote in message
> news:%23GYOu6VuHHA.2272@TK2MSFTNGP04.phx.gbl...
> Greetings,
>
> How do I import data from CSV file (source.csv) to my MSSQL table
> (dbo.target)?
>
> Kindly advise.
>
> Thanks
> ascll
>
>
>
>

[Database Administrator]

In that case, you'll need to import the file into a staging table, with a
varchar column instead of datetime. Then, you can do an INSERT SELECT into
the good table, filtering out the bad stuff in the WHERE clause.

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau


"Mike D" wrote in message
news:53F2CB48-AC63-4E4A-9C08-B202080E6D43@microsoft.com...
I have two csv files that I need import into MSSQL every 30 minutes. One
csv
I am able to perform an INSERT INTO table (column1, column2, etc) SELECT
column1, column2, etc. FROM OPENROWSET('MSDASQL', 'Driver={Microsoft Text
Driver (*.txt; *.csv)};DBQ=C:\DataFiles;', 'SELECT * from Materials.csv'),
however the other file has two columns with many invalid dates such as
00/00/0000 or 04/11/0704 (the first is output by SAP and the other is
fat-fingered). I know I need to read the file, but I am stuck as to how to
parse through it and remove any bad dates. BULK COPY won't help me on this.

Mike

"Tom Moreau" wrote:

> Check out the following topics in the BOL:
>
> BULK INSERT
> bcp.exe
>
> --
> Tom
>
> ----------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
>
> "nelson" wrote in message
> news:%23GYOu6VuHHA.2272@TK2MSFTNGP04.phx.gbl...
> Greetings,
>
> How do I import data from CSV file (source.csv) to my MSSQL table
> (dbo.target)?
>
> Kindly advise.
>
> Thanks
> ascll
>
>
>
>

[Database Administrator]

Thanks Tom for the reply. Would I use the same SqlBulkCopy but to a
non-esisting table or do I have to create it first?
--
Regards,

Mike D


"Tom Moreau" wrote:

> In that case, you'll need to import the file into a staging table, with a
> varchar column instead of datetime. Then, you can do an INSERT SELECT into
> the good table, filtering out the bad stuff in the WHERE clause.
>
> --
> Tom
>
> ----------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
>
> "Mike D" wrote in message
> news:53F2CB48-AC63-4E4A-9C08-B202080E6D43@microsoft.com...
> I have two csv files that I need import into MSSQL every 30 minutes. One
> csv
> I am able to perform an INSERT INTO table (column1, column2, etc) SELECT
> column1, column2, etc. FROM OPENROWSET('MSDASQL', 'Driver={Microsoft Text
> Driver (*.txt; *.csv)};DBQ=C:\DataFiles;', 'SELECT * from Materials.csv'),
> however the other file has two columns with many invalid dates such as
> 00/00/0000 or 04/11/0704 (the first is output by SAP and the other is
> fat-fingered). I know I need to read the file, but I am stuck as to how to
> parse through it and remove any bad dates. BULK COPY won't help me on this.
>
> Mike
>
> "Tom Moreau" wrote:
>
> > Check out the following topics in the BOL:
> >
> > BULK INSERT
> > bcp.exe
> >
> > --
> > Tom
> >
> > ----------------------------------------------------
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> > SQL Server MVP
> > Toronto, ON Canada
> > https://mvp.support.microsoft.com/profile/Tom.Moreau
> >
> >
> > "nelson" wrote in message
> > news:%23GYOu6VuHHA.2272@TK2MSFTNGP04.phx.gbl...
> > Greetings,
> >
> > How do I import data from CSV file (source.csv) to my MSSQL table
> > (dbo.target)?
> >
> > Kindly advise.
> >
> > Thanks
> > ascll
> >
> >
> >
> >
>
>

[Database Administrator]

The table must exist first.

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau


"Mike D" wrote in message
news:CF9CDAE3-D384-4864-B894-F24CDA8EAD9C@microsoft.com...
Thanks Tom for the reply. Would I use the same SqlBulkCopy but to a
non-esisting table or do I have to create it first?
--
Regards,

Mike D


"Tom Moreau" wrote:

> In that case, you'll need to import the file into a staging table, with a
> varchar column instead of datetime. Then, you can do an INSERT SELECT
> into
> the good table, filtering out the bad stuff in the WHERE clause.
>
> --
> Tom
>
> ----------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
>
> "Mike D" wrote in message
> news:53F2CB48-AC63-4E4A-9C08-B202080E6D43@microsoft.com...
> I have two csv files that I need import into MSSQL every 30 minutes. One
> csv
> I am able to perform an INSERT INTO table (column1, column2, etc) SELECT
> column1, column2, etc. FROM OPENROWSET('MSDASQL', 'Driver={Microsoft Text
> Driver (*.txt; *.csv)};DBQ=C:\DataFiles;', 'SELECT * from Materials.csv'),
> however the other file has two columns with many invalid dates such as
> 00/00/0000 or 04/11/0704 (the first is output by SAP and the other is
> fat-fingered). I know I need to read the file, but I am stuck as to how
> to
> parse through it and remove any bad dates. BULK COPY won't help me on
> this.
>
> Mike
>
> "Tom Moreau" wrote:
>
> > Check out the following topics in the BOL:
> >
> > BULK INSERT
> > bcp.exe
> >
> > --
> > Tom
> >
> > ----------------------------------------------------
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> > SQL Server MVP
> > Toronto, ON Canada
> > https://mvp.support.microsoft.com/profile/Tom.Moreau
> >
> >
> > "nelson" wrote in message
> > news:%23GYOu6VuHHA.2272@TK2MSFTNGP04.phx.gbl...
> > Greetings,
> >
> > How do I import data from CSV file (source.csv) to my MSSQL table
> > (dbo.target)?
> >
> > Kindly advise.
> >
> > Thanks
> > ascll
> >
> >
> >
> >
>
>

[tommonline]

Try http://www.sqlscripter.com to import text/csv data. There's a Csv to SQL converter and the data will be validated as well.

Cheers