FedEx Billing Online plus

[slpenney]

Has anyone worked with FedEx Billing online plus files within MS Access? I am having a variety of situations coming up and wondered if there was someone with experience that has used these files before.

Thanks, Stacie

[June7]

Never heard of them. What are they for? What do you want to do?

[slpenney]

It is an invoicing file used by FedEx (the courier) to provide data on shipments to their customers.

They have several fields that contain numbers stored as a text value and I'm trying to get them to convert to numbers. I've used VAL and that is fine when there is a number, however, there also are null values mixed in. I want to get the file into a table where I can complete calculations on them.

I cannot post data due to the company specific information it contains (and I"m not skilled enough to remove that information without potentially changing other parts of the file.)

Here's example of two columns from the .txt file:

"Earned Discount";"- 3.26";
"Performance Pricing";"- 8.91";
"";"";
"Fuel Surcharge";"1.23";

[ssanfu]

Here's example of two columns from the .txt file:

Two thoughts:

1) Import the .txt file to a temp table, run a delete query to delete the unwanted values, then process/import the remaining records.

2) Write code to open the .txt file, read a line, process (convert, change,etc), then insert into the proper table. Close the file.

[June7]

Can you do: Val(Nz([field],0))

[ItsMe]

My two cents

Import the entire file into Access. Use the wizard. Use the "Delimited Option". Choose "Semicolon" as the delimiter. Ensure the "Use Quotations as Qualifier" is checked. Indicate whether or not the first row contains header info. Save the import process with a meaningful name.

After it is imported, edit the table's data fields to the types you wish. If there are rows in the new table that contain extraneous data, you may need to run an append query to a permanent table. Determine if the original import procedure is clean enough to avoid an append query. The example you provided shows me an example of, semicolon delimited with quotation qualifiers.