Importing unformatted data
-
Hi i have about 30 files where i need to import the total sum number into my database.
The data in the files is unformatted and not in the structure you would normally load data into a SQL datbase in. Is there anyway i can do this ?
-
Ok, you seem to have left out a few key details that will be important in helping you. Please look carefully at your post and try to think of anything you can add that may be important to answering your questions.
-
Yes, SSIS or BCP can both work with semi-structured data.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Can you send an example of your data? Your post contains very limited information.
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/
-
Ok sorry i should have mentioned the types of files someof them are .txt and others a .csv. I'd like to use SSIS to import them in.
So With SSIS or BCP how can i go about this, I fine with importing structured, fix with ot delimited data but i've not done anything like this before.
-
You still haven't provided details about the structure of these files. The file extensions doesn't say too much. Show an example of your data?
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/ -
I will once i get a sample of it myself am awaiting it from another person. All i was told to have a look into seeing if this is possible to load a line of text from a file which is unformatted to be imported.
I have a idea what it will look like as it's an invoice.
So its will have a few lines of text at the head of the file
Then a few columns of data and then a summary which will have a Total amount, and its the total amount that i just need.
-
Yes, it is possible through SSIS.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
I would recommend that you use script task to extract the "Total" value.
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/ -
Ok i got a sample file through and its like i described above.
They are sales reports so i have a report heading with name and seq number & page number. Then i have the column headings and then the data under each heading. cust_code , qty, sales, gross profit & Value%
After this i 've page 2 of the data again a report heading seq no page number and column headings cust_code , qty, sales, gross profit & Value%
Then i've a summary of the cust_code , qty, sales, gross profit & Value% column heading per day and i have that total of these
qty sales profit % profi no of cust
TOTAL 3382 63385.78 18593.12 29.33 431
It's this line that i want to import into my table. this line number will be different for each file.
-
I see so there is no other way of doing this ? as am not a programmer now so this could be out of my league now.
-
It's doable. However, unless you can actually give us an example of the data (not a description, not the file extention) then it's not going to be possible to offer you anything more specific than that.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
You might be better off importing the entire file into a table with one column, one large varchar, and then searching through for this line, if the format is there. Then you can parse the line with charindex/patindex/substring.
Otherwise, you need to be a programmer.
Of course, if it's one line, you might be better off, and quicker to just open each file, find the line, and type the data in.
-
Steve Jones - Editor (11/25/2008)
You might be better off importing the entire file into a table with one column, one large varchar, and then searching through for this line, if the format is there. Then you can parse the line with charindex/patindex/substring.Otherwise, you need to be a programmer.
Of course, if it's one line, you might be better off, and quicker to just open each file, find the line, and type the data in.
You're also going to need to actually plough through (by hand) several sets of these files. Since you're looking at what sounds to be report files, you're going to want to be cautious that there are no SUB-total lines in there, that might look just like the "real" one you want.
In which case, you'd be looking to do something like Steve mentioned, but find ALL matches, and pull in the LAST one.
Or (which interestingly might actually be easier for SQL Server to do) - import the whole darned file and calculate the totals for yourself.......
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again? -
Can i attach a file here will i just paste in a smaple file.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply