| Author |
Topic |
|
Michael71
Posting Yak Master
126 Posts |
 Posted - 2007-01-18 : 14:12:19
|
| I'm trying to import data from a Excell database to a SqlServer Table...It ask me to write a type a SQL statement that will select data from the source table. Could some one tell me what SQL statement they are talking about? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-01-18 : 14:29:49
|
| There are three different methods that you can use to import an Excel file to a SQL Server table. I'll list them in the order of speed which the first being the fastest:1. T-SQL command: BULK INSERT2. bcp.exe3. DTSTry opening up the Excel file in notepad to see what format it is in. We'll need to know what the column delimiter and row delimiter is.Tara Kizer |
 |
|
|
Michael71
Posting Yak Master
126 Posts |
Posted - 2007-01-18 : 14:34:03
|
| What dou mean by column delimiter and row delimeter? I open it up in notepad |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-01-18 : 15:07:01
|
| Well there needs to be something that delimits/separate the columns and there must be some ending character for the rows. This information is needed in order to import the data. You can use a hex editor if you can't tell what the delimiters are.Tara Kizer |
|
|
|
Michael71
Posting Yak Master
126 Posts |
Posted - 2007-01-18 : 15:10:15
|
| It looks like a comma, but some has more than one comma. This is one row...out of 500 maybe..4707,1103,LIBERTY APPAREL COMPANY,201 DIKEMAN ST.,"BROOKLYN,NY 11231",,,,,,,,,,,,11106,11106,,,,,,0,,,,,,,5/2/2006,1146596814 |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-01-18 : 15:12:50
|
| When there are extra commas, that just means that there isn't a value for that column in that row.Try out the Import/Export Wizard in Enterprise Manager. It uses DTS. See if you can get the data imported that way. The other two methods will require a bit more work, although they are faster.Tara Kizer |
|
|
|
Michael71
Posting Yak Master
126 Posts |
Posted - 2007-01-18 : 16:35:24
|
| I'm getting a trucation error which I can't figure outExecuting (Error)MessagesError 0xc02020a1: Data Flow Task: Data conversion failed. The data conversion for column "Column 32" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.". (SQL Server Import and Export Wizard) Error 0xc020902a: Data Flow Task: The "output column "Column 32" (106)" failed because truncation occurred, and the truncation row disposition on "output column "Column 32" (106)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component. (SQL Server Import and Export Wizard) Error 0xc0202092: Data Flow Task: An error occurred while processing file "G:\PO_Header.csv" on data row 15. (SQL Server Import and Export Wizard) Error 0xc0047038: Data Flow Task: The PrimeOutput method on component "Source - PO_Header_csv" (1) returned error code 0xC0202092. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. (SQL Server Import and Export Wizard) Error 0xc0047021: Data Flow Task: Thread "SourceThread0" has exited with error code 0xC0047038. (SQL Server Import and Export Wizard) Error 0xc0047039: Data Flow Task: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown. (SQL Server Import and Export Wizard) Error 0xc0047021: Data Flow Task: Thread "WorkThread0" has exited with error code 0xC0047039. (SQL Server Import and Export Wizard) |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-01-18 : 16:42:20
|
| Either the Excel file isn't formatted properly for import or the table that it is being imported into doesn't match the format of the data.Tara Kizer |
|
|
|
rakesh4u
Starting Member
3 Posts |
Posted - 2008-09-04 : 08:38:53
|
| i want to load excel data into sql table using bcp, as specified above, if i open it in notepad, it is showing random data. please help. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2008-09-04 : 08:47:10
|
quote:
Originally posted by rakesh4u
i want to load excel data into sql table using bcp, as specified above, if i open it in notepad, it is showing random data. please help.
Tryhttp://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926MadhivananFailing to plan is Planning to fail |
|
|
|
|
Import a Excell file to SQL Server 2005 table