Hi everyone:

I am new to SQL and I need some advice. I just started a new job back at the first of April and I was excited to get back in the IT field again. My new boss, who is also the owner of the company, wants me to update his expensive, eloborate SQL-based database. Specifically, he wants me to update the item master table that is the catalyst of the this type of business, which is selling and distributing products. Basically, it is taking source fields from an external file and updating predefined target fields from a main table in the database. In a nut shell, it is creating a temporary table that needs to updated every quarter when we get the new item matrix, which includes all the info about each item we sell including list price, our cost, etc.

OK, now that I explained the background, the problem I am having is that one of our vendors that supplies us with product, has something called an Unitape to update our dB. It is a delimited file that is set up based on price structures that apply to us as a supply company. I tried to import it as an Excel *.CSV file but I can't seemed to get passed the set up of the file. It lists the item number on the first line with the main features of the item (Description, Prices, UPC codes, etc.) then on the second line it repeats the item number again except this time with the subsequent price points that we buy at and optional price levels to sell to our customers. Can someone give me some advice or has experience working with these difficult Unitape files?

I have tried to import them into Access and then create queries to build tables and such but extracting only the info we need when it is so dispersed throughout the file is a big problem. I have a data dictionary but not knowing how to separate rows, extract the info I need, and to delete the rows I don't need is such a huge challenge for a novice like myself (that's one of the reasons I bought the book "Beginning SQL" by Wilton and Colby). If anyone has any advice I would greatly appreciate it.

Thanks,
Jack Britton
Kent, WA

Hello

I am much near to your problem area. But wont't understand exactly where you are facing problem. Basically you have written in detail about your work area and not about the unitape format.
I wish you to paste a sample format of unitape file (as it is, what it contains), so that it will be easy for all to understand.
Now what you are doing is my daily practice about ".CSV" and get my work done easily using "excel" and "textpad". I have never faced any problem opening .csv in excel and textpad.
(Hope, you will get solution of your problem soon)

Regards
Milind

Milind:

I have used Access queries to break up the various fields from this unitape file to create a master table and imported it to an Excel spreadsheet to then turn it to a .CSV file. I think this will be my temporary solution until I find a better way to use this unitape text file. The fields are all concatenated and there is a master record on the first of each line for every new item number and the other lines are price structures(which I also created queries in Access to make individual price structure tables. An example of the format is as follows:

AAC47338C 47338C CLAY,SELF HARDENG,5LB,WE AACAMERAC 1802A0000M PK00001PK00006PK000330000001PK000001469092905 Y955237300039672473386PK00001NNUS 0000000
AAC47338C X5 04012006123120100101079970000000000 00000000000000000000010000014680000000000000000000 00000000000000000000000000000000000100000131900000 0200000131900000030000013190000000000000000 000000000000000
AAC47338C X4 04012006123120100101079970000000000 00000000000000000000010000014680000000000000000000 00000000000000000000000000000000000100000131900000 0200000131900000030000013190000000000000000 000000000000000
AAC47338C X3 04012006123120100101079970000000000 00000000000000000000010000014680000000000000000000 00000000000000000000000000000000000100000138600000 0200000138600000030000013390000000000000000 000000000000000
AAC47338C X2 04012006123120100101079970000000000 00000000000000000000010000014680000000000000000000 00000000000000000000000000000000000100000142500000 0200000142500000030000014130000000000000000 000000000000000
AAC47338C X1 04012006123120100101079970000000000 00000000000000000000010000014680000000000000000000 00000000000000000000000000000000000100000142500000 0200000142500000030000014250000000000000000 000000000000000
AAC47338C WE 04012006123120060106001980000000000 00000000000000000000010000014680000000000000000000 00000000000000000000000000000000000100000131900000 0000000000000000000000000000000000000000000 000000000000000
AAC47338C W5 04012006123120060106001980000000000 00000000000000000000010000014680000000000000000000 00000000000000000000000000000000000100000131900000 0000000000000000000000000000000000000000000 000000000000000
AAC47338C 9W 01012006123120100101079970000000000 00000000000000000000010000008520000000000000000000 00000000000000000000000000000000000000000000000000 0000000000000000000000000000000000000000000 000000000000000
AAC47339A 47339A CLAY,SELF HARDIND,25LB,WEAACAMERAC 1802A0000M PK00001PK00001PK000270000001PK000003999092905 Y955238100039672473393PK00001NNUS 0000000
AAC47339A X5 04012006123120100101079970000000000 00000000000000000000010000039980000000000000000000 00000000000000000000000000000000000100000359900000 0200000359900000030000035990000000000000000 000000000000000
AAC47339A X4 04012006123120100101079970000000000 00000000000000000000010000039980000000000000000000 00000000000000000000000000000000000100000359900000 0200000359900000030000035990000000000000000 000000000000000
AAC47339A X3 04012006123120100101079970000000000 00000000000000000000010000039980000000000000000000 00000000000000000000000000000000000100000378100000 0200000378100000030000036530000000000000000 000000000000000
AAC47339A X2 04012006123120100101079970000000000 00000000000000000000010000039980000000000000000000 00000000000000000000000000000000000100000387900000 0200000387900000030000038550000000000000000 000000000000000
AAC47339A X1 04012006123120100101079970000000000 00000000000000000000010000039980000000000000000000 00000000000000000000000000000000000100000387900000 0200000387900000030000038790000000000000000 000000000000000
AAC47339A WE 04012006123120060106001980000000000 00000000000000000000010000039980000000000000000000 00000000000000000000000000000000000100000359900000 0000000000000000000000000000000000000000000 000000000000000
AAC47339A W5 04012006123120060106001980000000000 00000000000000000000010000039980000000000000000000 00000000000000000000000000000000000100000359900000 0000000000000000000000000000000000000000000 000000000000000
AAC47339A 9W 01012006123120100101079970000000000 00000000000000000000010000023190000000000000000000 00000000000000000000000000000000000000000000000000 0000000000000000000000000000000000000000000 000000000000000


If you have any better suggestions, I am open.

Thanks,
Jack

Milind:

I found the answer to my problem. I used Access to make a table that extracted the various fields I needed from unitape and then exported that table as an Excel spreadsheet and then saved the Excel spreadsheet as an .csv file. Using a .csv in an Excel spreadsheet format is alot easier to work with than a text format with the SQL database that I am populating.

Thanks again,
Jack

Jack
Hello. I have just encountered a Unitape file for the first time. Would it be possible to explain, step by step, how this conversion process works? I have a copy of Access, but I am inexperienced with it.
Any help would be greatly appreciated.
Regards,
John
Chicago