Import Data from a Text or CSV file into SQL Server

I don't have permissions for Bulk Insert and need to use a lot of values in 'where' statement, that I'd like to read them from a file.
How can it be accomplished?

Thanks

Man ! I found this and thought, cool, I can do this. So I did it. But when I try the same thing with a different csv file, it doesn't work. For some reason, it fails to read the row terminator correctly and therefore, populates the entire csv data into the first row of the table.

After a few hours of testing and research, it turns out that SQL Server does not support Bulk Insert for csv files !

http://msdn.microsoft.com/en-us/library/ms188365.aspx[^]

Scroll down to INTEROPERABILITY: "Comma-separated value (CSV) files are not supported by SQL Server bulk-import operations. However, in some cases, a CSV file can be used as the data file for a bulk import of data into SQL Server. "

So it may work sometimes and sometimes, it may not. In other words, use at your own risk ...

Hey, great article can you help men with this… i had read a txt file with the bulk sintax but after that i find another problem, in my txt file it has the same header and detail in it so my problem now is how i can to put the header in a table1 and the detail in a table2, another thing i create just a field1 for put all my rows(header and detail), but they have FIELDTERMINATOR = ‘|’, but the header it has seven fields and the detail it has eleven fields so thats another problem because i cannot create just one table with the exact fields to put my detail and header… this an example of a txt file:

HR|001580/06|RG|11/01/2013 12:00|BG|3573|001580 IT|001580/01|1|00147066||1200|852.3|830.3|1.35|UNIDAD|0|31/12/2014 00:00 IT|001580/02|1|00147066||200|852.3|830.3|1.35|UNIDAD|1|31/12/2014 00:00 IT|001580/03|1|00147066||100|852.3|830.3|1.35|UNIDAD|55|31/12/2014 00:00 IT|001580/04|2|00254276||200|852.3|830.3|1.35|UNIDAD|0|31/12/2014 00:00 IT|001580/05|3|00305359||1700|852.3|830.3|1.35|UNIDAD|0|31/12/2014 00:00 IT|001580/06|3|00305359||300|852.3|830.3|1.35|UNIDAD|1|31/12/2014 00:00

and this is the query i have…

SQL

CREATE TABLE #temp
(
campo1 VARCHAR(max)
)
BULK INSERT #temp
FROM "
WITH
(
FIELDTERMINATOR = ‘|’,
ROWTERMINATOR = ‘\n’
)

This firs query it is for the header

SQL

SELECT *
FROM #temp
WHERE SUBSTRING(campo1,1,2) = ‘HR’

This firs query it is for the detail

SQL

SELECT *
FROM #temp
WHERE SUBSTRING(campo1,1,2) = ‘IT’

so then come my problem how i can Separate the fields for the header and the detail.

Hi,
Here is what I would suggest, for starters, I think you are inserting all your row data in a single column on #temp, what I would suggest is to create a hard table, with the appropriate structure, for example:

CREATE TABLE ImportData
(Campo1 varchar(200),
Campo2 varchar(200),
Campo3 varchar(200),
Campo4 varchar(200), etc. You should end up with 11 columns...)

I would make all the columns a string for simplicity.

Then you do your bulk insert in to the new table, provided you indicated the right number of columns, and the separator indicator is correct, you should have now is all the data in separate columns.

Not sure if this is a problem or not, but on the example you wrote, seems like you have a line with a header and detailed mixed, (the first line) because I see a, HR and IT rows in one line, this would be a problem; the source file would have to be modified to only have an HR or IT row per line.

Just FYI this is what I would to next:

I would create two separate tables, one for the header and one for the details, You probably want to start the table adding an Id column and define it as an identity key which will make each row unique, then you will insert the header first by doing:

INSERT INTO HeaderTableName ( [Your 7 Column List]...)
SELECT [Your Seven Column List from ImportData]
WHERE RTRIM(Campo1) = 'HR'

You won't include the Id column since that will be auto-populated by SQL Server.
This will separate all the header records. I'm suggesting you to use an ID but somehow you will need to have a column that will allow you to join the header and the detail, I think you already have field that does that, it seems that's your second field, what appears to be an order number, '001580' the problem is that you have other info in there (/60). So I would use the Id for easier joins in the future.

Once you have the header table, you need to insert into the Detail Table, again you will need to create a table with your 11 detail columns, and I would add two additional columns an Id which is an identity key which will make each row unique and another integer column which is a reference to your header. This will allow you to easily join the records of the two tables in the future.

To populate the table you do another INSERT SELECT with a JOIN

SQL

INSERT INTO DetailTableName ( HeaderId, [then your 11 detail columns] )
SELECT a.Id, [Your 11 Detail Columns from ImportData]...
FROM HeaderTableName a JOIN ImportData b ON ( Substring( Maybe the second field that refers to 001580 on HeaderTableName) = Subtring(again your second field in ImportData that contains 001580) )
WHERE RTRIM(b.Campo1) = 'IT'

Apologies, If I was trying to be too basic with my explanation, you may already know all about Ids and keys.

Hope this helps you somehow. Suerte.
Will

Thanks for the advice to Pondlife, i got the answer for my own question.
Here we go;

Well as we know i have this text file that in the same time has the header and the detail

HR|001580/06|RG|11/01/2013 12:00|BG|3573|001580|
IT|001580/01|1|00147066||1200|852.3|830.3|1.35|UNIDAD|0|31/12/2014 00:00
IT|001580/02|1|00147066||200|852.3|830.3|1.35|UNIDAD|1|31/12/2014 00:00
IT|001580/03|1|00147066||100|852.3|830.3|1.35|UNIDAD|55|31/12/2014 00:00
IT|001580/04|2|00254276||200|852.3|830.3|1.35|UNIDAD|0|31/12/2014 00:00
IT|001580/05|3|00305359||1700|852.3|830.3|1.35|UNIDAD|0|31/12/2014 00:00
IT|001580/06|3|00305359||300|852.3|830.3|1.35|UNIDAD|1|31/12/2014 00:00

So i need to:

1. Read the file
2. Insert the header into table1
3. Insert the detail into table2

For the First Step i create a table and then i use the Sql BulkInsert

SQL

CREATE TABLE #ImportData
    (
    	Field VARCHAR(max)
    )
    -- Insert the data into the first table
    BULK INSERT #ImportData
    FROM 'PATH FILE'
    WITH 
    ( 
        ROWTERMINATOR = '\n'
    )

i got something like this.

----------
We see that the rows it has delimiters( | ), that means the fields of the each table (HR - Header, IT - Details)

Then for my second and third step i need insert the rows on each table, but also i need to separate each fields of each row, so i create a procedure that receive a string and delimiter and return a table with each field of my string, i created the fields dynamically.

If you want to create the procedure you need to run this first

SQL

SET QUOTED_IDENTIFIER OFF

Then you can create it.

SQL

--EXEC spSplit
    --	@InputString = 'IT|001580/01|1|00147066||1200|852.3|830.3|1.35|QUANTITY|0|31/12/2014 00:00', -- VARCHAR(max)
    --	@Delimiter = '|' -- VARCHAR(50)
    
    --SET QUOTED_IDENTIFIER OFF
    ALTER PROCEDURE spSplit
    -- =============================================
    -- Author:		Gabriel Jiménez
    -- Create date: 2013-01-16
    -- Description:	Procedimiento creado para retornar
    -- valores de cada campo de una cadena delimitada 
    -- =============================================
    	@InputString	NVARCHAR(max),
        @Delimiter		VARCHAR(50)
    
    AS
    BEGIN
    
    	SET QUOTED_IDENTIFIER OFF
    
    	CREATE TABLE #Items (Field1 VARCHAR(max))
    
    	DECLARE @Item			VARCHAR(max)
    	DECLARE @ItemList		VARCHAR(max)
    	DECLARE @DelimIndex		INT
    	DECLARE @PositionField	INT 
    	DECLARE @SqlTable		VARCHAR(max)
    	DECLARE @SqlUpdate		VARCHAR(max)
    
    	SET @ItemList = @InputString
    	SET @DelimIndex = CHARINDEX(@Delimiter, @ItemList, 0)
    	SET @PositionField = 0
    
    	WHILE (@DelimIndex != 0) 
    	BEGIN
    	
    		SET @SqlTable = "ALTER TABLE #Items ADD Field"
    		SET @SqlUpdate = "UPDATE #Items SET Field"
    		
    		-- Set positions Rows and Fields 
    		SET @PositionField = @PositionField + 1
    		SET @Item = SUBSTRING(@ItemList, 0, @DelimIndex)
    		
    		IF @PositionField = 1
    		BEGIN
    			-- Insert the first data into the first field created on the table
    			INSERT INTO #Items (Field1) VALUES ( @Item )
    		END
    		ELSE
    		BEGIN
    			-- Create a dynamic table for the @InputString
    			SET @SqlTable = @SqlTable + CONVERT(VARCHAR, @PositionField)+ " VARCHAR(MAX)"
    			EXEC(@SqlTable)
    			
    			SET @SqlUpdate = @SqlUpdate + 
    				CONVERT(VARCHAR, @PositionField) + " = '" + 
    				CONVERT(VARCHAR, @Item) +"'"
    			EXEC(@SqlUpdate)
    			
    		END
    
    		-- Set @ItemList = @ItemList minus one less item
    		SET @ItemList = SUBSTRING(@ItemList, @DelimIndex+1, LEN(@ItemList)-@DelimIndex)
    		SET @DelimIndex = CHARINDEX(@Delimiter, @ItemList, 0)
    		
    	END -- End WHILE
    	
    	IF @Item IS NOT NULL -- At least one delimiter was encountered in @InputString
    	BEGIN
    		SET @SqlTable = "ALTER TABLE #Items ADD Field"
    		SET @SqlUpdate = "UPDATE #Items SET Field"
    		SET @SqlTable = @SqlTable + CONVERT(VARCHAR, @PositionField+1)+ " VARCHAR(MAX)"
    		EXEC(@SqlTable)
    		
    		SET @SqlUpdate = @SqlUpdate + 
    			CONVERT(VARCHAR, @PositionField+1) + " = '" + 
    			CONVERT(VARCHAR, @ItemList) +"'"
    		EXEC(@SqlUpdate)
    		
    	END
    	-- No delimiters were encountered in @InputString, so just return @InputString
    	ELSE 
    	BEGIN
    		SET @SqlTable = "ALTER TABLE #Items ADD Field"
    		SET @SqlUpdate = "UPDATE #Items SET Field"
    		SET @SqlTable = @SqlTable + CONVERT(VARCHAR, @PositionField+1)+ " VARCHAR(MAX)"
    		EXEC(@SqlTable)
    		
    		SET @SqlUpdate = @SqlUpdate + 
    			CONVERT(VARCHAR, @PositionField+1) + " = '" + 
    			CONVERT(VARCHAR, @InputString) +"'"
    		EXEC(@SqlUpdate)
    		
    	END 
    
    	SELECT * FROM #Items
    	
    	SET QUOTED_IDENTIFIER ON 
    END -- End Procedure
    GO

If you see i comment the three first lines for execute the procedure and you can get something like this

Well we not finish yet, we read the file, and we create the procedure that return the fields and now we need insert into each table, for that i create two temp table with static fields 'cause i know the fields of each table and then create Cursor for read each row on my #ImporData table.

SQL

CREATE TABLE #Header
    (
    	Field1 VARCHAR(max),
    	Field2 VARCHAR(max),
    	Field3 VARCHAR(max),
    	Field4 VARCHAR(max),
    	Field5 VARCHAR(max),
    	Field6 VARCHAR(max),
    	Field7 VARCHAR(max),
    	Field8 VARCHAR(max)
    )
    -- Detail table is for the detail items 
    CREATE TABLE #Detail
    (
    	Field1 VARCHAR(max),
    	Field2 VARCHAR(max),
    	Field3 VARCHAR(max),
    	Field4 VARCHAR(max),
    	Field5 VARCHAR(max),
    	Field6 VARCHAR(max),
    	Field7 VARCHAR(max),
    	Field8 VARCHAR(max),
    	Field9 VARCHAR(max),
    	Field10 VARCHAR(max),
    	Field11 VARCHAR(max),
    	Field12 VARCHAR(max)
    )

    DECLARE @Field NVARCHAR(max)
    DECLARE Header CURSOR        
        FOR SELECT  Field
            FROM    #ImportData
            WHERE SUBSTRING(Field,1,2) = 'HR'
    OPEN Header
    FETCH NEXT FROM Header INTO @Field
    
    WHILE @@FETCH_STATUS = 0           
        BEGIN 
    		INSERT INTO #Header
    		EXEC spSplit @Field, '|'
    		FETCH NEXT FROM Header INTO @Field
        END                              
    CLOSE Header
    DEALLOCATE Header
    
    --Detail
    --DECLARE @f NVARCHAR(max)
    DECLARE Detail CURSOR        
        FOR SELECT  Field
            FROM    #ImportData
            WHERE SUBSTRING(Field,1,2) = 'IT'
    OPEN Detail
    FETCH NEXT FROM Detail INTO @Field
    
    WHILE @@FETCH_STATUS = 0           
        BEGIN 
    		INSERT INTO #Detail
    		EXEC spSplit @Field, '|'
    		FETCH NEXT FROM Detail INTO @Field
        END                              
    CLOSE Detail
    DEALLOCATE Detail

    --SELECT * FROM #ImportData
    SELECT * FROM #Header
    SELECT * FROM #Detail
    
    DROP TABLE #ImportData
    DROP TABLE #Header
    DROP TABLE #Detail

After that if you put all the pieces on the same query you got this.

As i need to.

----------

thankyou for helping me Smile | :)

Import Data from a Text or CSV file into SQL Server

License

Comments and Discussions