How do I load text or csv file data into SQL Server?
If you need to load data into SQL Server (e.g. from log files, csv files, chat transcripts etc), then chances are, you're going to be making good friends with the BULK INSERT command.
You can specify row and column delimiters, making it easy to import files in comma-separated values (CSV), Tab-separated values (TSV), or any other delimiter (e.g. the pipe character is commonly used, since it rarely exists in valid data). You can also tell it to skip any number of header rows (these are fairly common in many log file formats).
So, let's run through a few examples. We have this table:
And let's say we have this CSV file, saced as c:\file1.csv:
The command to bulk insert this data would be:
Now, if we had tab-separated values, it would look like this:
Note that a row delimiter often has to be experimented with, because often log files produce "columns" in the table by putting the value and then the delimiter. So, in many cases, you might actually be looking to separate rows by column delimiter + row delimiter. So, the last column has a dangling column delimiter that isn't really necessary (but we have to deal with it). I currently have processes that look like this, to deal with files that have trailing tab characters after every column:
And earlier I mentioned the case where you have header rows that are almost never going to match the data types of the target table (and you wouldn't want the headers mixed in with the data, even if all the data types were character). So, let's say the CSV file actually looked like this:
If you try the BULK INSERT command from above, you will get this error:
This is because the first row doesn't contain valid data, and the insert fails. We could tell BULK INSERT to ignore the first row in the data file, by specifying the starting row with the FIRSTROW parameter:
Finally, you can also specify how many errors you want to allow before considering that the BULK INSERT failed. We can never really be confident that a log file from some external source will always have "perfect" data in it. Failures can occur when you have character data that is too big for the column in the table, or if you have a string in an integer column, or if your date is malformed, or several other potential scenarios. So, you probably want some threshold where more than n rows will cause the BULK INSERT to fail. You can specify this limit using the MAXERRORS parameter. (I've already requested for Yukon that they add a MAXERRORPERCENT so that there will be an appropriate failure ratio for both large and small files.)
You can also use bcp to move data from a flat file into an existing table. BULK INSERT and the bcp utility are documented in Books Online.
Related ArticlesAre there tools available for auditing changes to SQL Server data?
Can I create an index on a BIT column?
Can I have optional parameters to my stored procedures?
Can I implement an input mask in SQL Server?
Can I make SQL Server format dates and times for me?
Can I start IDENTITY values at a new seed?
Can SQL Server tell me which row was inserted most recently?
How can I learn more about undocumented SQL Server stored procedures?
How can I make my SQL queries case sensitive?
How do I audit changes to SQL Server data?
How do I connect to a non-default instance of SQL Server?
How do I connect to SQL Server on a port other than 1433?
How do I create a cross-tab (or "pivot") query?
How do I determine if a table exists in a SQL Server database?
How do I drop a SQL Server database?
How do I find all the available SQL Servers on my network?
How do I get a list of SQL Server tables and their row counts?
How do I get rid of Named Pipes / DBNMPNTW errors?
How do I get the correct date/time from the msdb.sysjob* tables?
How do I get the nth row in a SQL Server table?
How do I get the result of dynamic SQL into a variable?
How do I handle REPLACE() within an NTEXT column in SQL Server?
How do I hide system tables in SQL Server's Enterprise Manager?
How do I know which version of SQL Server I'm running?
How do I limit the number of rows returned in my resultset?
How do I manage changes in SQL Server objects?
How do I monitor SQL Server performance?
How do I prevent linked server errors?
How do I reclaim space in SQL Server?
How do I recover data from SQL Server's log files?
How do I search for special characters (e.g. %) in SQL Server?
How do I start SQL Server Agent from ASP?
How do I time my T-SQL code?
How do I upsize from Access to SQL Server?
How do I upsize my MSDE database to full-blown SQL Server 2000?
How do I use a variable in a TOP clause in SQL Server?
How do I use GETDATE() within a User-Defined Function (UDF)?
How should I store an IP address in SQL Server?
Schema: How do I find all the foreign keys in a database?
SQL Server & MSDE
What are reserved Access, ODBC and SQL Server keywords?
What are the capacities of Access, SQL Server, and MSDE?
What are the main differences between Access and SQL Server?
What do I need to know about SQL Server 2000 SP4?
Where else can I learn about SQL Server?
Where is SP4 for SQL Server 2000?
Why am I having problems with SQL Server 2000 SP3 / SP3a?
Why can't I install SQL Server on Windows Server 2003?
Why can't I install SQL Server on Windows XP?
Why can't I use LIKE '%datepart%' queries?
Why do I get "Login failed for user '\'."?
Why do I get 'object could not be found' or 'invalid object name'?
Why do I get errors about master..spt_values?
Why do I get script errors in Enterprise Manager's 'taskpad' view?
Why do I get SQLSetConnectAttr Failed errors?
Why do I have problems with views after altering the base table?
Why does EM crash when I get an error in a stored procedure?
Why does Enterprise Manager return 'Invalid cursor state'?
Why does my DELETE query not work?
Why does sp_spaceused return inaccurate values?
Why is Enterprise Manager slow at expanding my database list?
Why is my app slow after upgrading from SQL Server 7 to 2000?
Why is tempdb full, and how can I prevent this from happening?
Why should I consider using an auxiliary calendar table?
Why should I consider using an auxiliary numbers table?