wfoight
asked on
How to specify the "Unit Separator" in Format File
I am trying to import a file that is using a "Unit Separator" ... ASCII Value 31 ... as its delimiter. I am struggling with how to specify that value in a format file. I have tried many different values but none seem to work. Anyone run into this before and have a solution? My last attempt is shown below:
11.0
10
1 SQLCHAR 2 50 "\0x1F" 1 ContextID SQL_Latin1_General_CP1_CI_
2 SQLCHAR 2 50 "\0x1F" 2 ContextName SQL_Latin1_General_CP1_CI_
3 SQLCHAR 2 50 "\0x1F" 3 ContextParentContextID SQL_Latin1_General_CP1_CI_
4 SQLCHAR 2 50 "\0x1F" 4 ClientRecordNumberID SQL_Latin1_General_CP1_CI_
5 SQLCHAR 2 50 "\0x1F" 5 PatientID SQL_Latin1_General_CP1_CI_
6 SQLCHAR 2 75 "\0x1F" 6 CustomFieldName SQL_Latin1_General_CP1_CI_
7 SQLCHAR 2 50 "\0x1F" 7 CustomFieldType SQL_Latin1_General_CP1_CI_
8 SQLCHAR 2 125 "\0x1F" 8 CustomFieldValue SQL_Latin1_General_CP1_CI_
9 SQLCHAR 2 50 "\0x1F" 9 DeletedDateTime SQL_Latin1_General_CP1_CI_
10 SQLCHAR 2 50 "\0x1E" 10 DeletedBy SQL_Latin1_General_CP1_CI_
11.0
10
1 SQLCHAR 2 50 "\0x1F" 1 ContextID SQL_Latin1_General_CP1_CI_
2 SQLCHAR 2 50 "\0x1F" 2 ContextName SQL_Latin1_General_CP1_CI_
3 SQLCHAR 2 50 "\0x1F" 3 ContextParentContextID SQL_Latin1_General_CP1_CI_
4 SQLCHAR 2 50 "\0x1F" 4 ClientRecordNumberID SQL_Latin1_General_CP1_CI_
5 SQLCHAR 2 50 "\0x1F" 5 PatientID SQL_Latin1_General_CP1_CI_
6 SQLCHAR 2 75 "\0x1F" 6 CustomFieldName SQL_Latin1_General_CP1_CI_
7 SQLCHAR 2 50 "\0x1F" 7 CustomFieldType SQL_Latin1_General_CP1_CI_
8 SQLCHAR 2 125 "\0x1F" 8 CustomFieldValue SQL_Latin1_General_CP1_CI_
9 SQLCHAR 2 50 "\0x1F" 9 DeletedDateTime SQL_Latin1_General_CP1_CI_
10 SQLCHAR 2 50 "\0x1E" 10 DeletedBy SQL_Latin1_General_CP1_CI_
ASKER
That is the delimiter in the format file that I have been messing around with. Ultimately I am using openrowset to import the file:
SELECT a.*
FROM OPENROWSET(BULK N'C:\Temp\customdemographi
SELECT a.*
FROM OPENROWSET(BULK N'C:\Temp\customdemographi
How the file was exported? I'm asking this because perhaps you can change in the Export task instead of in the Import.
Another option is to edit the file and replace \0x1F with \0x09 (tab)
Another option is to edit the file and replace \0x1F with \0x09 (tab)
ASKER
Not sure how the file is exported. Some of the files contain comment fields with embedded carriage returns, single quotes, double quotes ... you name it. So changing the delimiter would not work in that instance. It would for the other files and then I could use SSIS to import the ones that cannot be changed. I have it working in SSIS ... just not as a bulk insert task. It is just a data pump. I would like to get it working outside of SSIS and simply use bulk insert.
This question needs an answer!
Become an EE member today
7 DAY FREE TRIALMembers can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
What are you using to import the data?