1

How do you import a table into a database from a text file when that table has /'s in the column names?

I have a SQL Server 2008 R2 table with columns that have /'s in their name. (SAP Database) For example: /BIC/FIC_SD001 has a column named /BIC/O_CST_00.

I have done an export (using the Import/Export Wizard) of this table to a text file and the slashes are there.

When I import the table into a different database (using the wizard) all the /'s from the column are removed and replaced with spaces. The above column now looks like this: BIC 0_CST_00

Thank you in advance for your help!

Sandra Rossi
  • 11,934
  • 5
  • 22
  • 48
Neil Hoff
  • 2,025
  • 4
  • 29
  • 53

2 Answers2

1

In the step of the wizard called Select Source Tables and Views, you have to click Edit Mappings... Over there you can manually change the Destination column names (setting back the missing slashes).

olmed0
  • 390
  • 1
  • 3
  • 13
  • You are right that would work, but I have 20 tables with around 40 columns per table that are like this. I should have been more specific in the question, Do you know of a scripted/automatic way to do this? – Neil Hoff Jan 06 '12 at 15:01
  • No, I can't think of an automatic solution. Actually, this is clearly a bug and I think you should talk directly to Microsoft. – olmed0 Jan 09 '12 at 08:47
1

You can use BCP Utility to import or export data from sql server to text file or vice verse.

    BCP Database.TableName out "Location of the text file " -c -S ServerName -T

The above command will load the data from sql server table to the flat file

To load the data from flat file to SQL server the command is as follows :-

    BCP Database.TableName in "Location of the text file " -c -S ServerName -T

The above 2 commands will work for Windows Authentication

praveen
  • 12,083
  • 1
  • 41
  • 49
  • I have tried BCP bwq./BIC/FIC_SD001 out "c:\temp\sd001" -c -S -T and BCP bwq."/BIC/FIC_SD001" out "c:\temp\sd001" -c -S -T. Both are getting the error: An error occurred while processing the command line. P.S. I put the actual server name for – Neil Hoff Jan 10 '12 at 22:35
  • If you have a trusted connection with the server, you can remove the -S flag. Otherwise, try -S without any blank space. E.g: -Smyserver – olmed0 Jan 11 '12 at 13:07
  • An example of BCP out command for Adventure Work database having windows authentication is as follows: BCP AdventureWorksLT.SalesLT.Address out "C:\temp\sd001" -c -S CBR-DEL1156\SQL2005 -T You will get that error if you don't write the database ,schema and the table name properly – praveen Jan 11 '12 at 13:57
  • Thanks for your help...I figured it out. I needed to add [] around db, schema and table. From a command prompt on the server here is what I typed: **BCP [BWQ].[bwq].[/BIC/FIC_SD001] out "c:\temp\sd001.txt" -c -T** – Neil Hoff Jan 12 '12 at 15:11