Wrox Programmer Forums

Need to download code?

View our list of code downloads.

| FAQ | Members List | Search | Today's Posts | Mark Forums Read
Classic ASP Basics For beginner programmers starting with "classic" ASP 3, pre-".NET." NOT for ASP.NET 1.0, 1.1, or 2.0
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Basics section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old July 12th, 2004, 03:54 PM
Authorized User
 
Join Date: Jun 2004
Location: , , .
Posts: 32
Thanks: 0
Thanked 0 Times in 0 Posts
Default Import data in SQL

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

Reply With Quote
  #2 (permalink)  
Old July 13th, 2004, 02:18 AM
Registered User
 
Join Date: Jul 2004
Location: Frövi, , Sweden.
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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




Reply With Quote
  #3 (permalink)  
Old July 13th, 2004, 08:31 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
Reply With Quote
  #4 (permalink)  
Old July 13th, 2004, 02:13 PM
Authorized User
 
Join Date: Jun 2004
Location: , , .
Posts: 32
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

Reply With Quote
  #5 (permalink)  
Old July 14th, 2004, 05:49 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
Reply With Quote
  #6 (permalink)  
Old July 14th, 2004, 11:44 AM
Authorized User
 
Join Date: Jun 2004
Location: , , .
Posts: 32
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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:)

Reply With Quote
  #7 (permalink)  
Old July 27th, 2004, 09:35 AM
Registered User
 
Join Date: Jul 2004
Location: Lahore, punjab, Pakistan.
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #8 (permalink)  
Old July 28th, 2004, 05:06 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
how to import excel data into sql server khaink ASP.NET 1.0 and 1.1 Basics 1 July 31st, 2007 09:10 PM
Import Data In MS SQL SERVER 2000 The Beginner Classic ASP Basics 1 August 14th, 2006 04:59 PM
Import Data remote SQL doanhvu BOOK: ASP.NET 2.0 Website Programming Problem Design Solution ISBN: 978-0-7645-8464-0 11 June 27th, 2006 02:46 PM
how to import data from excel,csv into sql server therisingsun ASP.NET 1.0 and 1.1 Basics 2 October 1st, 2005 12:17 AM



All times are GMT -4. The time now is 12:24 PM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.