how to cut data from a text file and save it in sql

Question

I need help :( i have a huge .txt file with more than 1 million records and ive already saved it into a sql server table the problem is that the file cames with spaces for example

id: 87936756                  name: asduyiasdjk cel:8735434            bla:erwerwerwe                        sdsd:sdfsdfsdf

and each row cames with a different size of spaces i tried using SUBSTRING but it doesnt work because each row cames with a different size of spaces and i need to save each row in a different column of a table in SQL Server

what i did was save the hole txt file into a single column in sql server i created a table called data with only 1 row called COLUMN and it looks like this:

123123123        NTGAMMA GROUP  S.A - -                                   CR 43      sdfsdfsdf - 188 IN 708                                                                                                                                                                                                                                  sdfsdfsdf     23423423423      2234234                         05                            -                        *99991231*CONSTRUCCION DE EDIFICACIONES PARA USO RESIDENCIAL

That's only the first row of the table DATA and it has over a million rows so how can I save each part of that COLUMN in a new table like I have to save that in a table called test and it has some rows like name telephone bla bla bla

Does SQL have something like split so I could cut all those spaces and save each data in the rows I need of the table test?

score 1 · Answer 1 · edited May 23 '17 at 12:28

You can update your column to a string containing comma separated values, removing all the extra white spaces by doing something like this ..

DECLARE @TestTable TABLE(Column1  NVARCHAR(MAX)) 

INSERT INTO @TestTable VALUES 
('id: 87936756                  name: asduyiasdjk cel:8735434            bla:erwerwerwe                       sdsd:sdfsdfsdf')

--Abort if necessary
IF EXISTS (SELECT 1 FROM @TestTable WHERE Column1 LIKE '%|%') RAISERROR('Special char exists in data',16,1)

UPDATE @TestTable
SET Column1 =  replace(
                  replace(
                     replace(
                        LTrim(RTrim(Column1)), 
                     '  ',' |'),                    
                  '| ',''),                         
               '|',',')

which will format your data something like this ...

SELECT * FROM @TestTable


╔════════════════════════════════════════════════════════════════════════════╗
║                                  Column1                                   ║
╠════════════════════════════════════════════════════════════════════════════╣
║ id: 87936756 ,name: asduyiasdjk cel:8735434 ,bla:erwerwerwe sdsd:sdfsdfsdf ║
╚════════════════════════════════════════════════════════════════════════════╝

Then you can split that data in one column into multiple columns

See here for more information.

Preface that with `IF EXISTS (SELECT 1 FROM @TestTable WHERE Column1 LIKE '%|%') RAISERROR('Special char exists in data',16,1)` — Anon, Jan 23 '14 at 16:00
@Anon very good suggestion indeed feel free to update/edit my answer I dont mind I am on phone now :S cant see a darn thing on this tiny screen — M.Ali, Jan 23 '14 at 16:07

how to cut data from a text file and save it in sql

1 Answers1