Importing Data to SQL 1

[Marryp]

Hello there

I am finding a way how to import data in SQL in the simplest way. I thought about using a VB script or using DTS. Somehow I think it will be better if I just use Stored Procedure or Access database.

Another problem I have is to format the data which is a .csv file.

Sample Data:
Group Type Member
Fruit Citrus Orange
Grapefruit
Sweet Peach
Drink Cold Water
Juice
Hot Tea
Coffee


How can I programmatically code the format of the data as I import it in SQL. The space should be filled with the proper values.

Thanks for the help.

 

[nigelrivett]

Simplest is bcp or bulk insert.
Insert into a staging table then insert into the table from there.



======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[bertrandkis]

There are 2 ways to import data from a Data file into an SQL Table.
You can use Bulk Insert or BCP programme.Suppose you have a csv (C:\My datafile.csv) file with the following data:

1111,Stone Age Books,Boston,MA,USA
2222 ,Harley & Davidson,Washington,DC,USA
3333 ,Infodata Algosystems,Berkeley,CA,USA

TO import using Bulk Insert, type this code in the Query analyser:

BULK INSERT Mydatabase..Mytable FROM 'C:\My datafile.csv'
WITH (
DATAFILETYPE = 'char',
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)

The bcp must be run from the command prompt,so you will need to open the dos Screen and type this:

bcp Mydatabase..Mytable in C:\My datafile.csv -c -t , -r \n -Sservername -Usa -Ppassword

the syntax of the bcp is as follows:
bcp <database and table name> <in|out> <Source File|destination file and bcp options> <connection credential to data source>

You can find out more about bcp and Bulk Insert by typing bulk Insert or bcp in BOL.

 

[Marryp]

But I want to format the data as I copy it in the server. From the example:
Group,Type,Member
Fruit,Citrus,Orange
, ,Grapefruit
, Sweet,Peach
Drink,Cold,Water
, ,Juice
, Hot,Tea
, ,Coffee

I want to specify the first line as the column names. Also on the example you will see that Grapefruit is under Fruit and Citrus and Peach is under Fruit but Sweet. This csv file is a report so I have to format the data when I copy to the server.

Please help.

 

[bertrandkis]

It is possible to format the data while transfering from the file to the table by specifying a format file while using BCP but you can only import into an Existing Table. If you want to use the fist row as your table fields it means then that your table does not exist, so in this case use DTS.With dts you can't go wrong because you are assisted by a wizard and it is a GUI tool. To launch your DTS wright click on your data base and select All tasks then chouse import data. Follow the guidance of the Wizard.You can save the package an re-run it later. If still not satified, please shout.
Bertrandkis

 

[Marryp]

Oh I see to use the BCP the table must be creted already.

Now the biggest question is the format of the the data. How can I do that thinking that table is created already and I will use BCP or in a scenario using DTS.

Fruit,Citrus,Orange
, ,Grapefruit
, Sweet,Peach

How will I code that Grapefruit is a Fruit and Citrus since the file is just showing it as Null?

Thanks again.

 

[bertrandkis]

When I import your data into sql using DTS I get this table
Group Type Member
------------------------
Fruit Citrus Orange
NULL Grapefruit
NULL Sweet Peach
Drink Cold Water
NULL Juice
NULL Hot Tea
NULL Coffee
So I suggest that you first import your data into sql and then you can do the manipulations from there.