Import data in SQL

Cinderella · July 12th, 2004, 03:54 PM

Hi guys!

I need help with importing data into an MS SQL database from Excel. The cell format in Excel is text. It contains telephone extensions. Here is one example of one field: 856,857,858. I want the data imported into SQL keep the comma between numbers. The destination field in SQL is in Varchar, 50. However, when I imported the data, the field with only numbers in it were fine, but the fields with numbers and commas and other punctuation marks were empty. Please help!

Cinderella

orphan · July 13th, 2004, 02:18 AM

Hello Cinderella
You dosen't tell how you try to do your import, so I asume it's a DTS job
And then is should work fine, at least it works fÃ¶r me.
If its not DTS, please post what kind och import precedure you are trying

/orphan

happygv · July 13th, 2004, 08:31 AM

Hi Cindrella,

what is the FieldDelimiter you are using? It shouldn't be COMMA.

Whatever way you try to import, use field delimiter as TAB, instead of using it as COMMA. That should take you there with no difficulty.

If using Query Analyser then use this

Code:

BULK INSERT Northwind.dbo.[Order Details]
FROM 'f:\FileName.XLS'
    WITH 
        (
            FIELDTERMINATOR = '\t',
            ROWTERMINATOR = '\n'
        )

Cheers!

_________________________
- Vijay G
Strive for Perfection

Cinderella · July 13th, 2004, 02:13 PM

Hi guys,

Thank you for replying! Unfortunately I have to use comma(,) and - (dash) because it's a telephone column. I used DTS Import/Export Data wizard to import the data. I created an excel sheet with only one column, cell format as text, and imported to a table in SQL with also only one column, data type Varchar, and it worked. Please advise. Thank you!

Cinderella

happygv · July 14th, 2004, 05:49 AM

Hi Cinderella,

I think you haven't got my previous post right. I was talking about not to use COMMA as FIELDDELIMITER. There can be comma within your field value.

Just found that in EXCEL that is treated as formatting of numbers. So on import from EXCEL to SQL server would result in loss of COMMAs.

As you say it is just one column you are trying to import, do the following.

1) Open Notepad file
2) Copy all the phone numbers from excel to notepad.
3) Save it as SOMETHING.CSV / .TXT

Then try this command in SQL Query analyser.

Code:

BULK INSERT YOURTABLENAME
FROM 'c:\something.csv' 
--NOTE : The file should be on the local hard disk of your SQL server, 
if you are remotely accessing your SQL server through SQL Query analyser
    WITH 
        (
            ROWTERMINATOR = '\n'
        )

OR Try the same with DTS Wizard.
1) Choose Data Source - Choose TEXT FILE from the drop down, and browse to choose your filename and click next
2) Select File Format - Choose DELIMITED Option button, and click next
3) Specify Column Delimiter - Choose TAB there and click next (should see the numbers with comma below in the preview).
3) I hope, the rest you can manage.

It is always better to use CSV or TXT formats for such imports as excel applys its default formatting and that doesn't reflect in your tables when imported.

Hope that explains better this time:).
Cheers!

_________________________
- Vijay G
Strive for Perfection

Cinderella · July 14th, 2004, 11:44 AM

Dearest happygv,

Once again you have saved me! I transferred the text file and it worked like magic! Thanks so much for your help!!!

Cinderella:)

adnan1 · July 27th, 2004, 09:35 AM

In my case your is post is applyable or not ?
i want to import database records from .mdf(foxpro database file).
when .mdf(foxpro database file) file upload, the records import to SQL server database.

*) It is possible when .mdf file upload(any folder of server) the records of file import to SQL server database

Thanks

happygv · July 28th, 2004, 05:06 PM

You can always export data from foxpro to text file, which can be then used for importing it into sql server. I am not sure if this is what you are trying to achieve. May be you can explain more on that to have a clear idea on what you are trying to do.

Are you trying to upload the mdf file to some where and have some scripts that export data from mdf files to sql server? If I have understood your post correctly... the data in mdf files different from data in ascii format(txt/csv), so you directly cannot read that from mdf file and import from within sql server. If you have foxpro installed in your pc, you can always write a program in foxpro that exports data from mdf files to txt/csv files that are comma seperated. And those txt/csv file can then be accessed from sql server and data can be ported to sql server.

Hope that helps
Cheers!

_________________________
- Vijay G
Strive for Perfection