how to import hundreds of CSV files into SQL server?

Hi,
I have about 500 CSV files, mostly they are unser same structure. I need to import them all into SQL server. I create a new table for each one based on the firstline as the column name. I can do them one by one but it is very time consuming and boring. Is there a fast way of doing this?

If someone give me a information, I promise to share all of MY CSV files. They are the zone chart from UPS and I have edit all of them.

Thanks.Well ... am working on the same myself ... will let you know as soon as i figure it out.|||Really? Someone here was complaining that it's nearly impossible to get your hands on updated info for this stuff!

Anyway, you'll have to be logged on with sysadmin fixed security role to do the following:

declare @.cmd varchar(8000)
create table #files (fname varchar(128) null)
insert #files exec master.dbo.xp_cmdshell [dir <your_directory> /b]
delete #files where fname is null

After this code is run you have a list of all the files that you want to import. If needed, you can incorporate a filter into your DIR command to filter out anything you don't want.

Now you're ready to build your loop to BULK INSERT each file.|||Now you're ready to build your loop to BULK INSERT each file.

Well .. bulk insert requires the table to be present and we both do not have that luxury ... have posted on this before and did not get a satisfactory answer ... so need to think of something else

http://www.dbforums.com/showthread.php?threadid=980175|||I'll send you a VB app to do this...|||I was working on a DTS package using a Dynamic Properties tast to pull file names from a table, and update the path of a Text Input file, but getting it to iterate is the kicker...|||The main problem for me is not to get it to iterate but to get the size of the columns and the column names.

Excel uploads are better coz you can make a linked server to them.|||With a Text Input file in a DTS package, you can set it up to pull the first row as column names. Sizes are, I believe, automatic, but probably defaults to NVARCHAR(255).|||So, how would you like to determine the field size?|||Well, I BULK INSERT into the same table, into either TEXT or IMAGE field. Actually, I have more than 1 table, and I am dealing with archiving EDI transactions coming from/going to the state. IMAGE datatype I use to store all their PDF's and DOC's with their daily ammendments, and TEXT field for the actual transaction files. I also implemented archiving on the tables with TEXT field, so that the database doesn't have to be a tera-byte size for 2 years worth of transactions to be available.|||Dynamically based on the data ...

In case of an excel .. i am using a opendatasource query and then issuing a select * into ... but am not able to do that on csv and flat files|||but if it's a CSV, then the length of a field can change from line to line. I see that working only if it is a fixed-length field file.|||Thats exactly the problem ... and DTS is not an option for me|||Working on it...|||Is it okay if the fields default to some larger size (VARCHAR(255)), then you can shrink them manually?|||How can you load a table that does not exist?

How are you going to know the layout to build it?

I still say that for flat files you either generate a format file and bcp it in, or load it all in to a table with 1 column varchar(8000) and parse the data in to the destination table...

csv is easy if the tables already built...

why aren't the tables built?

How are you going to derive the structure of the file?

on the fly?|||Try this.

Not too flashy, certainly not rock solid, but it should work.

Customize the connection string first, replacing <servername> and <databasename>.

Browse to the folder holding the text files (it will iterate through all files, so be sure you only have CSV files there).

It creates a table named after the text file, adds fields using the first row (all varchar(255)), and runs through the file inserting data.|||I only had two small csv files to test with. Let me know if there are any major bugs.

If you really want, I can go through the file contents first to find the MAX size of each field before creating the table, but I want to go home tonight...|||I was working on the UPS calculator for a while and forget anything else. I will test the zip file that bpdwork created and let you know what happened.

I have the calculator down. The database is kind of big, I don't know how to let everyone share it. send me a mail if you need to take a look.

How to import from text file

Hi. Basic question: how do you import from a .csv file into a database table? Thank you.

See SQL Server 2000, 2005 Books Online topic:
bcp Utility

or check out DTS/SSIS import tasks

|||You can also use bulk insert.|||

DTS is best to go when you are not sure about few things that are new to you.

http://www.microsoft.com/technet/prodtechnol/speech/library/loganalysis/log_files_UsingDTS.mspx

KBA http://support.microsoft.com/kb/285829

How to import data from a .csv file

I'm a SQL newbie and am wondering what is the correct procedure to
import data from .csv file into a SQL table. I want to electronically
clear checks in MS Dynamics GP and need to update one field in the
checks table to indicate that the check has cleared.
Any help is appreciated.
Best regards,
Frank Hamelly, MCP
NOVA Solutions LLC
Melbourne, FLfhamelly@.cfl.rr.com wrote:
> I'm a SQL newbie and am wondering what is the correct procedure to
> import data from .csv file into a SQL table. I want to electronically
> clear checks in MS Dynamics GP and need to update one field in the
> checks table to indicate that the check has cleared.
> Any help is appreciated.
> Best regards,
> Frank Hamelly, MCP
> NOVA Solutions LLC
> Melbourne, FL
>
Hi Frank,
You can do it either via DTS/SSIS (depending on which SQL server version
you are running) or you can create a linked server in SQL server and
then create a query that can select values from the file and then update
your tables.
Try to look for "importing data" in Books On Line - that should get you
started.
Regards
Steen Schlter Persson
Database Administrator / System Administrator|||Thank you for the help Steen.
Frank

How to import data from a .csv file

I'm a SQL newbie and am wondering what is the correct procedure to
import data from .csv file into a SQL table. I want to electronically
clear checks in MS Dynamics GP and need to update one field in the
checks table to indicate that the check has cleared.
Any help is appreciated.
Best regards,
Frank Hamelly, MCP
NOVA Solutions LLC
Melbourne, FLfhamelly@.cfl.rr.com wrote:
> I'm a SQL newbie and am wondering what is the correct procedure to
> import data from .csv file into a SQL table. I want to electronically
> clear checks in MS Dynamics GP and need to update one field in the
> checks table to indicate that the check has cleared.
> Any help is appreciated.
> Best regards,
> Frank Hamelly, MCP
> NOVA Solutions LLC
> Melbourne, FL
>
Hi Frank,
You can do it either via DTS/SSIS (depending on which SQL server version
you are running) or you can create a linked server in SQL server and
then create a query that can select values from the file and then update
your tables.
Try to look for "importing data" in Books On Line - that should get you
started.
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator|||Thank you for the help Steen.
Frank

How to import data from a .csv file

I'm a SQL newbie and am wondering what is the correct procedure to
import data from .csv file into a SQL table. I want to electronically
clear checks in MS Dynamics GP and need to update one field in the
checks table to indicate that the check has cleared.
Any help is appreciated.
Best regards,
Frank Hamelly, MCP
NOVA Solutions LLC
Melbourne, FL
Thank you for the help Steen.
Frank

How to import csv to database

Hi,

How to import csv file to sql server databse?Can anybody have the code?

Regards

Brijesh singh

You may use DTS (in SQL Server 2000) or SSIS (in SQL Server 2005).

How?
Checkout this link:http://www.sqldts.com/276.aspx

Good luck.

|||

you can find hereSpeed Up Copy Operations With SqlBulkCopy - Sql Server - CSV through sqlBulkCopy...[ADO.NET 2.0]

Good Luck./.

how to import csv file into sqldatabase

Hi All,

I have to import a csv file to sql database.I searched on the net and found plenty of exampls but all with msaccess database.I did not find any example with

sql server.Can anybody have the code or the link?here is one example with msaccess

Dim cn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\;Extended Properties=""Text;HDR=No;FMT=Delimited""")

Dim da As New OleDbDataAdapter()

Dim ds As New DataSet()

Dim cd As New OleDbCommand("SELECT * FROM C:\Test.csv, cn)

cn.Open()
da.SelectCommand = cd
ds.Clear()
da.Fill(ds, "CSV")
dg.DataSource = ds.Tables(0)
cn.Close()

but when i am using the sqlserver how to get csv data by setting extended properties .

Regards

Brijesh Singh

Wy cant u try in sqlserver

bulk insert patientidx
from 'd:\IDX export1.csv'
WITH
(
FIELDTERMINATOR = ','

ROWTERMINATOR = '\n'
)

Here patientidx is the tablename

Thank u

Please remember to click "Mark as Answer" on this post if it helped you.

How to Import CSV file in to SQL table

Any body know How to Import CSV file in to SQL table using asp.netYou can read the file into a DataSet using ADO.NET. See this article for details:
http://www.devarticles.com/c/a/ASP.NET/Reading-a-Delimited-File-Using-ASP.Net-and-VB.Net/2/

Then use that DataSet to update the SQL Server table using a data adapter.|||The old style bcp still works at SQL 2000. This is command for Data Im/Export since SQL 4
At DOS prompt, type bcp to see all help information|||alright i read through the article and it helps alot
the only question that i have is
some csv's have commas in certain fields even if it isn't a break but to signify it, it uses "" around the statement such as
fname,lname,notes,bob,stevens,"he likes things, NOW"

and it would return
fname lname notes
bob stevens he likes things, NOW

here is my code

 Private Sub btnImportCSV_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnImportCSV.Click
 Dim FileToRead As String
 FileToRead = Server.MapPath("import.csv")
 Dim FileStream As StreamReader
 filestream = File.OpenText(FileToRead)
 Dim ReadContents As String
 ReadContents = FileStream.ReadToEnd()
 Dim TextDelimiter As String
 TextDelimiter = ","
 Dim SplitOut = Split(ReadContents, TextDelimiter)
 Dim i As Integer
 For i = 0 To UBound(SplitOut)
 btnImportCSV.Text = SplitOut(i)
 Next
 End Sub

it works and it works nice but my only problem is that each field that contains commas is split
does anyone know how to fix this or make the delimiter work better
thanks for your help|||ALL this example is to display CSV on webform but I want to save CSV in to Database , can anybody help me !!!!!|||The problem is the text file uses both commas and double-quotes " as delimiters. If it did one or the other it would not be a problem. Is there any way to change the format of the delimited file?|||kalpesh2000
to input this into a db
you already have the data
all you need to do is know that this array has a certian amount of fields

such as, this is your table
fname, lname, address, zip,
Bob, James, 123 Rd., 43234,
doug, Brown, 321 St., 83943

This would be your data
SplitOut(0)="fname"
SplitOut(1)="lname"
SplitOut(2)="address"
SplitOut(3)="zip"
SplitOut(4)="Bob"
SplitOut(5)="James"
SplitOut(6)="123 Rd."
SplitOut(7)="43234"
SplitOut(8)="doug"
SplitOut(9)="Brown"
SplitOut(10)="321 St."
SplitOut(11)="83943"

then you just need to make an SQLcommand that inserts into the database 1 record
then you will want to skip the first four because they are the headers
so you will say (i dont remember the syntax for the increment for the for statement so look that up)

for x=4 to UBound(SplitOut) increment=4
sqlcommand.parameters("@.firstfield").value=splitout(x+0)
sqlcommand.parameters("@.SecondField").value=splitout(x+1)
sqlcommand.parameters("@.ThirdField").value=splitout(x+2)
sqlcommand.parameters("@.fourthField").value=splitout(x+3)

connection.open
sqlcommand.executenonquery
connection.close
next x

this code is all untested just a representation of how it should work|||Regarding blackdisk's example:

It doesn't work because it assumes that commas are the delimiters, when according to his example it is a mix of commas *and* double-quotes. That's the sticking point.

To the original question:
Is there any way you can alter the flat file? Can you re-export it from whatever program, for example?|||if my data was all Strictly comma delimited it would function correctly as my example shows it should work
probably not very efficiently but would work

i did not know what control i had over the data and i will be able to define the fields and NO Comma restrictions so that will be fixed

thank you all for your help|||the correct for statement is
for x=4 to UBound(SplitOut) Step 4|||what is the syntax for a the enter command

(for a delimiter)|||If you mean a line break, you can use either "\n" or Environment.Newline|||Hi Darrell

In one of your responses you mentioned using a dataset to import the contents of a CSV file into a SQL database. I have managed to use a dataset to read the contents of the file and produce the datatable, but I am now stuck as how to use this datatable/dataset to transfer this information into a SQL database.

Could you give any further guidance?

Many Thanks|||Pass the DataSet to a properly configured data adapter, then call the data adapter's Update method.

For help on Data Adapters, check out the following MSDN docs:

Creating Data Adapters Using Server Explorer
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon/html/vbtskCreatingDataAdaptersUsingServerExplorer.asp

Creating Data Adapters Using a Wizard
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon/html/vbtskCreatingDataAdaptersUsingWizard.asp

Creating and Configuring Data Adapters Manually
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon/html/vbtskCreatingConfiguringDataAdaptersManually.asp

How to Import and Export CSV File

Hi frnz ,

i want to know how to import a CSV File and export CSV File From database SQLSERVER-2005 Using ASP.NET With C#

IF any one knows Help me

Vinnu

http://www.codeproject.com/aspnet/ImportExportCSV.asp

How to hide/Remove the first row of the CSV file?

I have created one report and exported in CSV file. But in CSV file, it
shows the Textbox names, table names etc in the first row. i dont want
to show the first row of the CSV result. any idea how to hide/Remove
the first row?
Regards,
VinodHmmm...tough one...
I would say you should setup a link to print another report that is the
same data but w/o the headers...There is probably a better solution but
this one would be fairly easy. If its something that you are scheduling
to be created on a share or sent out w/o headers, why include them on
the report at all?
Hope this helps!
--
Ben Sullins
http://bensullins.com