Compiling CSV files

[steelcoyot]

I'm looking to see if anyone know's how to take a csv file, scrub it for data and push it into a new file. What I have is an Accounts Recievable file that is pushed out by my database software that has a list of items sold to each department. What I want is something that will take the data, add up the totals and push it to another file with a column for the department, accounts payable, accounts receivable, invoice number, invoice total and date.

Thanks

 

[xicana]

I think I know what you're trying to do - I had to do this once...is the AR file that you are getting including detail like so:

Invoice# InvoiceDate Item#1 Qty Price ExtendedAmt
Invoice# InvoiceDate Item#2 Qty Price ExtendedAmt
Invoice# InvoiceDate Item#3 Qty Price ExtendedAmt

If you are getting an AR file - how will you populate the AP column?

Anyway - what I did was bring the text file that looked like what i listed above into Access and created a query where I grouped by Invoice# and summed up the extended amounts...is that what you are trying to do?

Sandy

 

[steelcoyot]

Actually here are the headers for the columns for the ar export file

Store ID, Sales person, Invoice, item ID, Description, cost, total amount of items, date, number of distribution, GL Account.

What I need is away to combine the the invoices so instead of listing all the items sold it'll give use a total:

So what I need to import is this:
Date, Number of Distribution, gl account, discription, amount.

The problem I see is the export file has it broken down by store id and then the general GL account and what we need is the total and have it referance the actually gl account. Meaning in the export we have 39000 and what I'll need is 39000-00-10-55.

 

[DeMann]

I think that you can use xicana's solution, but instead of grouping by invoice number, group by GL Account, description and date perhaps, and sum the number of distribution and amount.

Are you having trouble grouping? Or figuring out what to group? It seems like there would be a fairly simple solution.

Thanks.

_______________
[cN]

 

[xicana]

I agree with DeMann - if you need help with grouping in a Query we can guide you in the right direction.

"The problem I see is the export file has it broken down by store id and then the general GL account and what we need is the total and have it referance the actually gl account. Meaning in the export we have 39000 and what I'll need is 39000-00-10-55."

I don't see how you can get the ACTUAL GL account number if you don't have it included in the CSV file coming from your accounting software - unless you can modify the export from the accouting software to include the exact GL account. Are you exporting from Invoice History? If so, do you know for a fact that the exact GL account is being stored with the Invoice?



Sandy

 

[steelcoyot]

Actually I'm getting the export from my production software which can't handle the multiple GL Accounts, What I was thinking of doing is seeing if there was a way I can have it look at column A and then Column d and take that information and populate it on a seperate page.

 

[xicana]

I'm not sure what you're trying to do anymore...maybe if the situation was a little more clear...maybe you can explain what exactly you're trying to do - don't assume we know anything.

Sandy