Importing a CSV into SQL sever

Transact-SQL
,
1

Hi ,

I am trying to import a csv file into sql server tables.

This is how the sample text file looks like

100001-XX Test1
5/12/2016 4:39:59 PM
Label1,Round, P,460.0000, Q,74.0000, R,672.0000, I,1.0000, J,.0000, K,.0000, V,16.0000, L,20.0000
Label2,Round, P,460.8000, Q,74.0000, R,672.0000, I,-1.0000, J,.0000, K,.0000, V,16.0000, L,20.0000
Label3,Round, P,458.2000, Q,265.0000, R,530.0000, I,1.0000, J,.0000, K,.0000, V,13.0000, L,19.0000
Label4,Round, P,459.0000, Q,265.0000, R,530.0000, I,-1.0000, J,.0000, K,.0000, V,13.0000, L,19.0000
Label5,Square, P,480.2000, Q,-265.0000, R,530.0000, I,1.0000, J,.0000, K,.0000, V,, L,
Label6,Square, P,459.0000, Q,-265.0000, R,530.0000, I,-1.0000, J,.0000, K,.0000, V,, L,
Label7,Square, P,460.0000, Q,-74.0000, R,672.0000, I,1.0000, J,.0000, K,.0000, V,, L,,
Label8,Square, P,460.8000, Q,-74.0000, R,672.0000, I,-1.0000, J,.0000, K,.0000, V,, L,
Label9,Rect, P,4568.5435, Q,330.0000, R,348.0000, I,-.1736, J,.0000, K,-.9848, V,, L,
Label10,Tri, P,4569.0645, Q,330.0000, R,350.9544, I,.9848, J,.0000, K,-.1736, V,, L,
Label11,Rect, P,458.5435, Q,18.5731, R,348.0000, I,-.1736, J,.0000, K,-.9848, V,
Label12,Tri, P,4569.0645, Q,128.5731, R,350.9544, I,.9848, J,.0000, K,-.1736, V,, L,
Label13,Rect, P,458.5435, Q,-72.8538, R,348.0000, I,-.1736, J,.0000, K,-.9848, V,
Label14,Tri, P,459.0645, Q,-72.8538, R,350.9544, I,.9848, J,.0000, K,-.1736, V,, L,

I have created three tables Table1 , Table2 and Table3

The first two lines should go into the first table as
Name ------- Description------- Date
100001-XX ----- Test1 ----- 7/12/2016 6:39:59 PM

from 3rd row onwards, The first two columns should go into Table2
Label ----- Label_description

Label1 ----- Round
Label2 ---- Round
Label3 ------ Round
....
....

everything after 2nd column should go into the Table3 as:

Values ---- Values Descr

460.0000 --- P
460.8000 --- Q
74.0000 --- R
530.0000 --- I
-1.0000 --- J
.0000 --- K
-.9848 --- V
74.0000 --- L
672.0000 --- P
1.0000 --- Q
.0000 --- R
.0000 --- I
16.0000 --- J
20.0000 --- K
.....
....
.....
Not sure how to achieve this. Please help.

Thanks in advance!!

2

One way is to import the data into a working table then split as desired.

If this is an ongoing process, you might look into building a front end (like VB). I did that to import Excel files because I got tired of Microsoft using wrong datatypes.

1 Like