May 29, 2013 at 2:36 am
Hi Experts,
i have a text file with 1 -3 lakh records and 5 columns or so.... each column is seperated by tab delimiter.
i need to import this file to SQLSERVER 2008.
while importing this text file into SQL server, the first column in text file has 10 characters string
i need to split this string into 4 separate strings of fixed lengths (like1st string -2char, 2nd string - 2char, 3rd string - 3char, 4th string -- remaining chars) and insert it into 4 different columns in sql server.
example i have a string in first column like INAPHYD00001 when i import this into sql server it should split like
IN ------to be inserted into Country column in sql server.
AP -----to be inserted into State column in sql server.
HYD ----to be inserted into City column in sql server.
00001 --to be inserted into LocalityID column in sql server.
i heard i can use Bulk Copy but i'm not sure how to use it and also how to prepare a format file for this requirement.
please help me in this regard.
Thanks
Kishore
May 29, 2013 at 9:42 am
kishorefeb28 (5/29/2013)
Hi Experts,i have a text file with 1 -3 lakh records and 5 columns or so.... each column is seperated by tab delimiter.
i need to import this file to SQLSERVER 2008.
while importing this text file into SQL server, the first column in text file has 10 characters string
i need to split this string into 4 separate strings of fixed lengths (like1st string -2char, 2nd string - 2char, 3rd string - 3char, 4th string -- remaining chars) and insert it into 4 different columns in sql server.
example i have a string in first column like INAPHYD00001 when i import this into sql server it should split like
IN ------to be inserted into Country column in sql server.
AP -----to be inserted into State column in sql server.
HYD ----to be inserted into City column in sql server.
00001 --to be inserted into LocalityID column in sql server.
i heard i can use Bulk Copy but i'm not sure how to use it and also how to prepare a format file for this requirement.
please help me in this regard.
I'm not sure what a "lakh" record is but if this is simply a one-off, you could import the file as is into a holding table then just use TSQL to manipulate the data to suit and insert into the permanent table.
Otherwise, if it's an on-going task then I would think an SSIS Solution would be more maintainable.
May 29, 2013 at 4:43 pm
If you post a couple of sample rows of data and the DDL for the table you are trying to BCP into, you'll get better help more quickly.
May 30, 2013 at 1:04 am
Thank you David,
here are the sample rows
first row:
IN010000000040011 20000101GOODS AND SERVICES TAX 100000202 1 0.00 99999 010 0.0500000000000.00 0.00 0.00000000 0.00000 0.00000
seconrow:
IR0100000000AK2231 20000101IND TAX - GOODS AND SERVICES TAX 100000202 1 0.00 99999 010 0.0500000000000.00 0.00 0.00000000 0.00000 0.00000
there are possibilities where the column number varies and delimiters between columns doesn't follow a pattern.
TABLE DDL:
1. Country
2. State
3. City
4. Locality (all these 4 columns should be loaded by splitting the first columnfrom the text file).
5. Description
6. CustNUM
7. ID
8. RngFrom
9. RngTo
10. percentage
11. val1
12. val2
13. val3
14. val4
datatypes: 1-6 Nvarchar. 7-12 Number.
among all the columns i just need to split the first column in text file and insert into 4 different columns in database as said in above post and remaining columns can be inserted directly.
please help me in this regard.
thank you.
May 30, 2013 at 5:57 am
kish DDL for a table would look more like this, having the compelte datatypes:
what you posted cannot be copied and pasted into SSMS, na dwouldn't help us build a testable solution for you.
also the data you pasted does not have tabs delimiting the data; it looks like spaces, or is completely missing (ie no tab between what i think are two separate values: 20000101GOODS AND SERVICES TAX)
CREATE TABLE MyTable(
Country varchar(max) ,
State varchar(max) ,
City varchar(max) ,
Locality varchar(max) ,
Description varchar(max) ,
CustNUM varchar(max) ,
ID varchar(max) ,
RngFrom varchar(max) ,
RngTo varchar(max) ,
percentage varchar(max),
val1 varchar(max) ,
val2 varchar(max) ,
val3 varchar(max) ,
val4 varchar(max))
Lowell
May 30, 2013 at 10:01 am
If the number of columns changes by row and the delimiters aren't constant, BCP is not really a good solution for this. You could bcp the whole row into a staging table and then parse the row into the appropriate columns using substrings and other functions, but Lowell's observation on the values that look like they are running together with no delimiter between is completely accurate. If these values are in fixed column positions, you can probably handle that. If these are supposed to be delimited by something and are not really, or the delimiter can change between rows, I can't think of a good way to make that work reliably. Do you have any control over the way this data is extracted and delivered to you?
May 31, 2013 at 8:49 am
In India
[font="Courier New"]Lakh 10^5 (100,000)
Crore 10^7 (10,000,000)[/font]
Seems so natural to original poster, but not to the rest of the world.:cool:
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply