May 20, 2008 at 1:49 pm
Hi,
I have a probem and I am not sure how this is done...At work today I spoke to someone about rewriting an application at work. Here is what i need to figure out...
From head office they send a flat file to an application that in turn sends the flat file to us, the flat files contain order information. My idea was to bypass the other software and take the original flat file and put that into a sql database that i could then use the data that is from the flat file.. then in turn send back the newly inputted data ( from the flat file to sql ) and make that into a flat file and send it back...
So my quesion is how would i go about taking the flat file and putting it into a sql database? I can probably figure out how to take it back out of sql and put it to a flat file...
here is a partial of the flat file:
GRNT0 OHMR0010319R040517040517P8485
GRNT0GRADES:
GRNT2 03145206=152458
GRNT2 040000632846/0080573207
GRNT2 060080573207
GRNT2 070005005464-BG B58330-000 CANOLA OIL RB
GRNT2 08CPT-DESTINATION
GRNT2 090000480133
GRNT2 12TTKE TORONTO TANK LINES
GRNT1 xy xxP8485
GRNT1RATES:
GRNT0
Now in the first line there is more information..i.e tare, net, gross weights. Each line has more data to it but I could not add those because the info wouldn't be read correctly on this page.
that would be one order. Now I would need to extract that info and put to a database...The head office seems to swear by these flat files, i think changing the head office to use a simpler version (xml) would be useless
So how would i go about doing this? ( adding the flat file info to sql db )
If i can get this figured out it would be a great help
thanks
Chris
May 20, 2008 at 1:55 pm
So are these flat files fixed length files with one line entry representing one row in one table? If so, this is a pretty straight forward bcp (with a format file) or use DTS to read in the file using the text file source connection object.
May 20, 2008 at 2:12 pm
Hi John,
From what it looks like it wouldn't be one line entry representing one row. The reason why i suspect this is because there is other data on some of the lines. I would have to send you a complete order entry to see what i mean. But from what i am looking it that it would be multiple rows per some of the lines.
If you care to take a look at a complete order entry I could send you a copy. I wish the head office would just use a sql database or at least an xml file instead of a text file. But to change the head office out of something that they swear by would just not happen.
But once i have this figured out it would put me on the fast track to want i want to do, the rest that i need to do would be simple stuff.
Chris
May 20, 2008 at 2:23 pm
That's kind of what I thought based on the data you posted. This probably means also that you'd want to import the data into more than one table in your database. This is still a pretty straight forward task using DTS.
What I would suggest you do is to get a file definition doc of some sort and come up with your data model to match the different data elements in the file. Once you have that done, you'll have to use DTS to handle the file. This may involve a fair amount of ActiveX scripting, but nothing too terribly difficult.
If you can (unless someone else has a way better idea), develop your data model and get a better understanding for the rules of the file's data elements (i.e. how the data in the rows are used to identify where it belongs) and then post an updated description of what needs done on this thread and we'll see if we can get you going.
May 20, 2008 at 2:34 pm
Is there a way by reading each row to uniquely identify what data is in each row?
😎
May 20, 2008 at 2:36 pm
Hi John,
I should get file definition of the flat file so i can see would go where when it gets back to head office.
What i originally thought about doing was using a streamreader and a delimiter of blank spaces, to catch the other data on the lines then write that to an xml file then go from there adding it to tables in my database.
You are right I would be using more than one table so i can keep copies of it on my end. When i go back to work tomorrow i will ask about the file def. If i can get the files elements then that would prove easier because maybe this could be done line by line.
chris
May 20, 2008 at 2:42 pm
Hi Lynn,
I see what you are saying..after me thinking for a moment, I do realize what the elements are in the flat file, I was told this when i seen the flatfile.
I.E one line has the tare, gross, net weights
another order number etc...
I should have paid more attention when i was being told this. but i got over excited about doing this that i got lost in thought about what i wanted to do rather than paying attention when i was being told what is what in the file.
When I look at this flat file it looks all jumbled, pretty messy.
Chris
May 20, 2008 at 2:51 pm
Hi again,
I just took a look at what the head office's flat file that they send to us..it is more readable. Now that i looked at this file, I can replicate what they send to us..its the flatfile that we send back that looks messy...that was what as confusing me...but i am going to give it a go tonight and read abut this DTS
Thanks you two..I'll keep you posted on the progress.
Chris
May 20, 2008 at 3:02 pm
Yea, that's what I was getting at when I said 'how the data in the rows are used to identify where it belongs'. Most files like this that I've dealt with have a data element that identifies the record type for that row. It's usually the first data element in each row.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply