Question on Importing .CSV through VI

[hermes418]

I've moved much farther along on my problem; I'm trying to import tracking numbers for existing orders into the SO_19 table through the VI; I have a CSV file that contains the SO number and the tracking number; my difficulty is this--my .csv does not have "package numbers" as they exist in MAS, and therefore my operation fails whenever I try to run it through VI.

Anyone have any suggestions on how I could import these items; any tricks in VI?

 

[Blorf]

Hi.

Does your CSV have any reference that can be traced back to the records in SO_19? I am thinking you export from your shipping program, use some routine to validate data in SO_19 to your csv, then run your export. If you can link them in any way, I think we can write the routine.

Thanks,
ChaZ

There Are 10 Types Of People In The world:
Those That Understand BINARY And Those That Don’t.

 

[hermes418]

I do have a validation item; the Sales Order Number; my difficulty is that the .csv I get out of Worldship just has
SO nubmer and tracking numbers, and MAS wants SO, package and tracking. For example, I get

SO Number Tracking number
888 z41
888 z47
889 z54
890 z57

And to go into MAS, I need
SO Number Package Tracking number
888 1 z41
888 2 z47
889 1 z54
890 1 z57

Now, the easy part is that the MAS package number really are that simple and sequential, so if I just had some way on import to have it assign a counted value (starting with one) for each SO's packages, that would work--but I'm totally at a loss on how to accomplish this.

 

[Blorf]

So you need simple to add 1,2,3.. for each so in your csv? Starting over with each new SO in your csv?

If so, how can you determine there will not be 2 records for 888 on Monday, and one more on Tuesday?

Because of that, I think you need to peek into so19, see the max package number for each so, and start there plus 1 for each so.

That being said, do you have ms access available on the work station that will run the job?

If so, I can help you write a routine to do this. Read the csv, peek into Mas, and re-output the csv with the correct info, and auto start the vi job.

Let me know,
ChaZ

There Are 10 Types Of People In The world:
Those That Understand BINARY And Those That Don’t.

 

[hermes418]

Looking back in won't be a problem--the company only ships when the order is complete. Yes, I would love some help with writing this--and Access is on the shipping machine.

 

[Blorf]

Post a few lines of one of the csv files if you don't mind.

Thanks,
ChaZ

There Are 10 Types Of People In The world:
Those That Understand BINARY And Those That Don’t.

 

[Blorf]

BTW, what is the OS of the shipping machine? XP hopefully.

Thanks,
ChaZ

There Are 10 Types Of People In The world:
Those That Understand BINARY And Those That Don’t.

 

[hermes418]

Machine is XP Pro, here are s few .csv lines (columns are not labeled in the .csv):
SO # Track #

86901 1Z961W080359110208
87610 1Z961W080359703612
87610 1Z961W080359129421
87692 1Z961W080360123639
87632 1Z961W080361462244
87632 1Z961W080361561253

 

[Blorf]

Hi. Is the data text qualified? Also, CSV usually has comas, are there there? "Comma Seperated Values" and what not?

Like ...

"86901", "1Z961W080359110208"

Thanks,
ChaZ



There Are 10 Types Of People In The world:
Those That Understand BINARY And Those That Don’t.

 

[hermes418]

Sorry; yes, it is just a standard .csv, like so:

"0086901",,"1Z961W080359110208"
"0087610",,"1Z961W080359703612"
"0087610",,"1Z961W080359129421"
"0087692",,"1Z961W080360123639"
"0087632",,"1Z961W080361462244"
"0087632",,"1Z961W080361561253"
"0087689",,"1Z961W080359076667

 

[Blorf]

Cool.

What is the second comma after the SO for? Looks like three fields there, but one is blank. Is that where you want your Package number?

Thanks,
ChaZ

There Are 10 Types Of People In The world:
Those That Understand BINARY And Those That Don’t.

 

[hermes418]

The Worldship tech built the automated export file--I guess he did put a blank field in there. That would be the location where we want our package number.

 

[Blorf]

Hi Hermes. I wrote a quick vb script to alter your text file, instead of using access. I think it should work for you.

If you create a new text doc using note pad, and paste the below into it and save it as "Blah.VBS", you should be able to just double click the file you created, and it will read your csv, and create a new one that is corrected.

Change "C:\Test.CSV" to your actual file name, "Include Quotes"

Change "C:\Fixed.CSV" to the name of the file you want created.

Hope it helps,
ChaZ

'------------------------------------------

Const ForReading = 1, ForWriting = 2
Set objWS = CreateObject("WScript.Shell")
Set objFSO = CreateObject("Scripting.FileSystemObject")

Set objReadInput = objFSO.OpenTextFile("C:\Test.CSV", ForReading, True)
Set NewFile = objFSO.OpenTextFile("C:\Fixed.CSV", ForWriting, True)

M_SO = ""
SO_Count = 0
Do Until objReadInput.AtEndOfStream
Tstr = objReadInput.ReadLine
if left (Tstr, 9) <> M_SO then
M_SO = left (Tstr, 9)
SO_Count = 0
End if

SO_Count = SO_Count + 1
New_L = left (Tstr, 10) & chr(34) & (So_Count) & chr(34) & ","
New_L = New_L & mid (Tstr, 12, 100)
NewFile.writeline New_L
loop

There Are 10 Types Of People In The world:
Those That Understand BINARY And Those That Don’t.

 

[hermes418]

Okay, the code works great (and I am incredibly grateful), assigns me all the number I need, but when I run the import into MAS I still have a problem. Data goes in, I can run a Crystal eport and see that all three fields have been populated; however, when I go into MAS and run a shipping inquiry on the orders, I still don't have the tracking number appearing in the tracking screen.

Any ideas?

 

[Blorf]

Hi. Maybe post your job and lets take a look.

ChaZ

There Are 10 Types Of People In The world:
Those That Understand BINARY And Those That Don’t.

 

[Blorf]

I just took a look at the data dictionary listing, and I see that so_19 expects an invoice number, not a SO number. This being said, you will likely have to cross reference the so to the invoice to get this to work.

ChaZ

There Are 10 Types Of People In The world:
Those That Understand BINARY And Those That Don’t.

 

[hermes418]

I was mistaken when posting earlier--the number I am pulling out of Worldship is the invoice number, I just am so used to working with the SO module that I called it an SO number--it is indeed the invoice number.

 

[Blorf]

Do you have access to Libriary Master - Repair?

I would suggest runing a rebuild sort and key files on so 19 and see if that fixes it.

Good news if it does, but also bad news, because it would mean just another case of a flaw in the VI module.

I have several jobs that require a sort rebuild before the imports will take effect.

Let me know,
ChaZ

There Are 10 Types Of People In The world:
Those That Understand BINARY And Those That Don’t.

 

[hermes418]

Here is my import job:

CONFIGURATION INFORMATION
-------------------------------------------------------------------------------------------------------------
FILE NAME: SO.INVTRAK S/O INVCE DATA ENTRY TRACKING
IMPORT FILE: F:\Access Databases\upsexportfixed.CSV
FILE TYPE: DELIMITED
INSERT ALL REQUIRED FIELDS DURING SETUP: Y
PASSWORD: NO

DATA ITEMS
-------------------------------------------------------------------------------------------------------------
ITEM ITEM NAME FG TYPE OPERATION COLUMN IMPORT MASK SUB-STRING
-------------------------------------------------------------------------------------------------------------
00001 INVOICE_NUMBER S STRING REPLACE 00001 Start: 1 Len: 7 No
00002 PKG_NUMBER S ZERO-FILL REPLACE 00002 Start: 1 Len: 4 No
00003 TRACKING_ID S STRING REPLACE 00003 Start: 1 Len: 30 No

VALIDATION RECORDS
-------------------------------------------------------------------------------------------------------------
ITEM FIELD METHOD SKIP FILE/STRING/EXPRESSION
-------------------------------------------------------------------------------------------------------------
00001 INVOICE_NUMBER EXPRESSION N SO_19$(1,7)<>DIM(7)
00002 PKG_NUMBER EXPRESSION N SO_19$(8,4)>="0000"
00003 TRACKING_ID STRING Y


 

[Blorf]

All I see is that field Send_PKG_To_SI is listed as required in the data dictionary. Maybe import, with that filed defaulted to Y and see what happens.

Also the sort and key thing I mentioned.

ChaZ

There Are 10 Types Of People In The world:
Those That Understand BINARY And Those That Don’t.