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?

 

[hermes418]

Added the "Send_PKG_To_SI" field--don't see any result so far. I don't have access to rebuild, they'll have to try that on Monday.

Thanks so much for your help--we'll see what happens when they get in on Monday and rebuild.

 

[hermes418]

Okay, much progress has been made; here is what I've got:

1) I need to be importing in AR_19, not SO_19.
2) Unfortunately, AR_19 does require an invoice number and not an SO number, so now I've got my import job straightened out, and we still need the VB applet to add the package numbers, but I've got to come up with a way to cross those SO number with the INV numbers.

 

[Blorf]

You mentioned a few posts ago that you ship all so's complete, so one invoice, one sales order, always.

Can you confirm that?

Assuming it's so, I can help solve the problem. My solution probably isn't the best there is, but I would read your csv into access, query mas (Which would involve someone being there to enter the company, thier user id, and password) and do the look up.

Let me know.

ChaZ



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

 

[hermes418]

I have confirmed that they only do it that way. Do I need to start building an access database?

 

[Blorf]

To be absolutely fair, using access may not be the best way, but it's the only way I know how.

That being said, yes.

Are you familiar with using the ODBC connection in an access database?

Thanks,
ChaZ

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

 

[hermes418]

Familiar enough--I've now set up an empty database with an ODBC connection to MAS's ARN Invoice history header.

 

[Blorf]

Hi.

I would not link to ARN, because it is possible to have multiple invoices to one SO, although you say that will never happen. Either way, to ensure you are always working with the correct SO, I would link to SO_03SOHistoryHeader, which you can get the field "LastInvoiceNumber" from. This way, you will always be using the most recent Invoice number for your sales order when you post your data.

You will also need to link to your CSV file.

When you have done that, we will write a query to get the invoice number from SO_3, based on the sales order number from your .csv file.

When you link your text file, it will ask questions like field type and field names. When you are done, post it and I will aid in building the query and automating.

Thanks,
ChaZ






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

 

[hermes418]

Okay, I've linked in SO_3 and the export, linked them by SO number, and built a query to pull SO,Package,Tracking and "last invoice number", which I presume is the proper field. What would you like me to post?

 

[Blorf]

Well, nothing. I was not aware you were familiar with Access enough to write the query. I was going to help build the query, but that doesn't seem to be necessary.

Were there matching records in SO_3 for all your records in your csv?

ChaZ

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

 

[hermes418]

So far on my first two tests.

 

[hermes418]

I am the happiest boy in the world! Exported the query into a .csv and ran my import and finally--success!

Now, just an automation question--do you know any way to automate that export operation, or are they going to have to manually export that query everyday? (I have hit the end of my Access knowledge--I've never had to automate anything on it before).

 

[Blorf]

Hi.

Yes, we can automate.

First, you are calling the VBS file to add the numbers, yes?

Either way, select forms, and create a new form. Do not use the wizard, just design view.

When the forms comes up, right click anywhere, and select properties. A properties thingie will come up. Now, select the edit menu, and find "Select Form" which will make the properties window show the properties for the form.

Now, in properties, select the "Events" tab.

Under events, find "On Open" and select it. Two buttons will appear to the right, one with "...". Hit that one, and a new thing will appear. Select Code Builder. You will see something like

Option Compare Database

Private Sub Form_Open(Cancel As Integer)

End Sub

Your curser will be just above "End Sub"

There, type the following

DoCmd.TransferText acExportDelim, , "QueryName", "C:\YourFile.CSV"

Replace "QueryName" with what ever you named your query. Include the quotes of course. Also, C:\YourFile.CSV needs to change to what ever you want it to be.

OK, now save your form, and give it a name. Something like "Main"

Now, select tools -> Startup. On the right hand side, near the top, it asks for a form to load at startup. Use the drop down to select the form you just created, and you should be set.

Also, if you like, after the docmd.transfertext line, if you add Quit, it will exit Access after the file is created.

Let me know how it goes. Sorry for the long post, but wanted to be as clear as I could.

Thanks,
ChaZ

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

 

[hermes418]

It's working very well, except that if I try to set the destination to a sub-folder of a network drive (F:\tracking rather than F I get a runtime error 3625. Any ideas?

 

[Blorf]

Hi. Can you post the line of code for me?

Also, does it give a description with error 3625?

Some things to consider:

Does the user in question have rights to write data to the sub-folder?

Are you sure the SubFolder exists?

The output to field should say "F:\blah\mydoc.csv" with the back slashes in the correct places (I am sure they are, but have to ask)

Lastly are yhou sure F is mapped? If not, maybe use \\Myservername\blah\myfile.csv instead?

ChaZ

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

 

[hermes418]

Got it fixed--I did need a UNC path; problems with the old workstation they've got my remoting into.

One last question; is there any way to automate the VI job so that it will run daily? I;ve never had to do that before--I've always just run big individual jobs. The manual says that the system can be set to cycle, but only if run through the command line?

 

[Blorf]

Hi. By using the windows scheduler, you can auto start your access application. The access application can run your vbs script, then run the query export, then invoke the VI Job.

Basicly, My solution is to create a .bat dos batch script, and invoke it from code.

The batch file would say something like this (Change to your path and your vi job number of course)

M:
CD\
CD MAS90\HOME
M:\MAS90\HOME\PVXWIN32.EXE ..\launcher\sota.ini ..\soa\mas90 -ARG VIWI0V W O DIRECT DISPLAY DTC


Hope that helps,
ChaZ



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

 

[hermes418]

And that's it! The job is done. I want to thank you so much for all of your help--I have learned quite a bit through this process--you have been great!