Weekly Transaction Processing

[Ralph Howarth]

I have a download report in *.csv that I want to use MS Excel to convert into
another *.csv every week. The purpose is to prepare transactions for
importing into accounting software as new transactions.

The report download name is made up of a text string that is related to a
User ID such that the report is received as "ReportName<UserID>.csv" The
worksheet in the report is the same name as the file name. The report has
the data header on row 3 and the transaction records begin at row 4 to a
variable number of rows.

1) How can that data be read and replaced in a worksheet of a reusable
workbook such that old data of a previous copy paste is removed and fresh
data laid down so formulas can read the new data?
2) How can all references to the report file be mass modified when the
UserID changes?

Thanks a bunch,
Ralph

 

[Harlan Grove]

Ralph Howarth wrote...

I have a download report in *.csv that I want to use MS Excel to convert into
another *.csv every week. The purpose is to prepare transactions for
importing into accounting software as new transactions.

The report download name is made up of a text string that is related to a
User ID such that the report is received as "ReportName<UserID>.csv" The
worksheet in the report is the same name as the file name. The report has
the data header on row 3 and the transaction records begin at row 4 to a
variable number of rows.

1) How can that data be read and replaced in a worksheet of a reusable
workbook such that old data of a previous copy paste is removed and fresh
data laid down so formulas can read the new data?
2) How can all references to the report file be mass modified when the
UserID changes?

You're transforming one text file to another text file. Spreadsheets
are not the best tool for doing this. Even if the transformation is
fairly complicated, you could write a filter program in VBScript (which
is almost certainly installed on your machine) to transform the CSV
file. Without details of what exactly you're doing to the text file,
that's all I'll say about that.

For #1, if you never save the .XLS file that has the formulas that
convert the original CSV file into the transformed CSV file, but only
save the transformed CSV file, then you could just close and reopen
this .XLS file for each report. Otherwise, all it takes is clicking in
the box in the top-left of the worksheet frame (to the left of the
column letters and above the row numbers) and press [Delete]. Are you
looking for a macro?

For #2, if you're copying the original CSV files into an .XLS file, and
if previously transformed CSV files were cleared from the .XLS file's
'import' worksheet, why would you have any references to the CSV file
to change?

You need to provide more details about how you want this to work.

For #1 and #2, are you opening the CSV file as a separate workbook then
copying it into a worksheet in the workbook that creates the new CSV
file? Are you then generating the new CSV file in a different worksheet
of that workbook?

 

[Ralph Howarth]

**Inline Commented following

Ralph Howarth wrote...

You're transforming one text file to another text file. Spreadsheets
are not the best tool for doing this. Even if the transformation is
fairly complicated, you could write a filter program in VBScript (which
is almost certainly installed on your machine) to transform the CSV
file. Without details of what exactly you're doing to the text file,
that's all I'll say about that.

**I did not realize VBScript could do that. And, yes, what I am doing is
certainly complicated; and if VBScript is easier then I'm for that; but I am
not versed in VBScript at the moment, nor other “average†user. As I will
note below in detail, I essentially have attempted to have an XLS file read
the downloaded CSV file, have the formulas reference the data in that CSV
file upon opening up the XLS file (not opening the source CSV file if I can
avoid it), and then simply do an export out as another, transformed CSV. The
reason for the routine data conversion is that the download file is "generic"
and the specifications for importing the data into an accounting system has
to have the data rearranged and / or data type formatted.

For #1, if you never save the .XLS file that has the formulas that
convert the original CSV file into the transformed CSV file, but only
save the transformed CSV file, then you could just close and reopen
this .XLS file for each report. Otherwise, all it takes is clicking in
the box in the top-left of the worksheet frame (to the left of the
column letters and above the row numbers) and press [Delete]. Are you
looking for a macro?

**Since I was planning on exporting the “transformed†CSV from the
“Conversion†worksheet I essentially am doing the reopen/close method you
recommend. I hope to avoid having to copy / paste the data into a worksheet
of the XLS for the purpose of a conversion worksheet to read with formulas.
I would rather have the source CSV read directly. I did manage to achieve
that; but advanced features like the OFFSET function seems to not work if the
source CSV file is closed. I also run into the source CSV filename changing
based on a UserID. As the “Conversion†worksheet is nothing but formulas
that would update from the source CSV of a “dynamic†filename, I find that I
have to, at the moment, open the source file, copy, and paste into an
“import†worksheet. From there I have the formulas reference the import
worksheet instead of the source CSV. In that case, your suggestion for mass
Delete is helpful as a workaround.

For #2, if you're copying the original CSV files into an .XLS file, and
if previously transformed CSV files were cleared from the .XLS file's
'import' worksheet, why would you have any references to the CSV file
to change?

** Again, I hope to avoid having to copy the original CSV file but the file
name changes based on the UserID that the report is sent to. The filename of
the original CSV is concatenated like this:

“ReportNameâ€+UserID+“.csvâ€

The worksheet name of the original CSV is also the filename since CSV does
not know Excel and Excel seems to presume the worksheet name to be the
filename.

I also have a “Setup†worksheet in the XLS. There I have some Lookup tables
to help covert the data (and the lookup tables work fine) but I also added a
place for the UserID to be entered. I was hoping that I could have someone
enter their UserID on the Setup worksheet and then I could concatenate the
value into the matching filename that formulas would reference to. I have
not found a way for a formula to accept a value as literal text in a
reference to the external source CSV. I have even tried having another cell
concatenate the filename based on a user’s input of their UserID number and
Defined a Name as “FileNameâ€. Then I tried to have formulas reference
FileName in different ways like:

= ‘[FileName]FileName’!colrow
or
= ‘FileName’!colrow
…and a host of different variant attempts…

but I would tend to be prompted a Lookup (Filename) window to go find the
file manually.

It did not work to well.

You need to provide more details about how you want this to work.

** I hope I am “painting†a better picture so far.

For #1 and #2, are you opening the CSV file as a separate workbook then
copying it into a worksheet in the workbook that creates the new CSV
file? Are you then generating the new CSV file in a different worksheet
of that workbook?

** Originally I was opening up the CSV file as a separate workbook and then
I had the XLS file utilize the open CSV “workbook†for reference in formulas
residing a Conversion worksheet. It is the results of the formulas that I
planned on exporting out as another CSV. Now I have resorted to copying the
CSV as a separate worksheet in the XLS for the time being. When I generate
the new CSV, I export it out as its own “workbook†instead of adding the
“results†on as a different worksheet.

However is the best way to do it, my goal is to:

a) Have a user open the file for the first time and enter initial setup data
on the Setup worksheet. The Setup worksheet essentially has two parts: One,
for mapping of the generic values to the meaningful values a user’s
accounting system will recognize for importing purposes; and, Two for
entering in the UserID so that formulas in the Conversion worksheet can find
the source data to convert from by concatenating the expected filename of the
source CSV. I use Lookup formulas on the Conversion worksheet to reference
the mapping done by the user on the Setup worksheet. This permits changing a
generic fund name assigned by a third party into an actual fund name and fund
codes used by some accounting software…I use Lookup tables to achieve that
altogether. The UserID issue is what I am stuck on. If I can accomplish
this, then I do not have to make custom XLS files for each user or be stuck
with copy and paste in each XLS to make it work.
b) For weekly transactions, the user would download the weeks transactions
as the source CSV where the filename is always the same for the user so the
download is what effectively purges the old data by overwriting the same
filename in the same folder location. Upon download, have the user simply
open the conversion XLS, click on either a Macro, or have a Macro run an
event on Open, so the Conversion worksheet may export to the transformed CSV.
As the source CSV will have a varied number of transactions I also hope to
gain dynamic ranging ability so worksheet formulas are not referencing blanks
or the reverse, data rows exist beyond the scope of rows formulas are in.
c) The user then opens up the accounting application (a Delphi/paradox
platform) and performs the import on the transformed CSV (as that is the file
format the accounting software will accept…not an XLS).

If this is easier outside of Excel, then I’m for that, else what could be
the best Excel method tool? Since I am likely going to have users who do not
know VBScript some users, as well as computer systems administrators, may
need training on using / installing VBScript?

 

[Harlan Grove]

If you want to avoid copy & paste into the new CSV-generating XLS, then open
each original CSV file, immediately save under a dummy filename, e.g.,

C:\Windows\Temp\foobar.CSV

Then open your XLS file in which you would have changed all file references
from <whatever> to C:\Windows\Temp\[foobar.CSV]. Generate the new CSV file,
close both files WITHOUT SAVING EITHER, and repeat the process with the next
original CSV file. You could use another XLS file with macros to automate
this process.

 

[Ralph Howarth]

Thank you,

That would work...having the references point to a dummy file; but I found a
little bit of a better option for swapping out the reference links:

By choosing Edit/Links/Change Source, I can have the whole Conversion.XLS
switch the file reference to that of the new source CSV.

Since the users would not be handling anyone else's download files, then it
would be just a matter of updating the links once per user and then all the
downloads thereafter would always be the same filename. As the download is
performed every week the download will lay down the new data over the old by
a complete file replacement.

I also found that the XLS will not read a closed CSV automatically like how
an XLS can read a closed XLS. So I found that opening the source CSV is
necessary...which is not too bad as that does offer the user a means to
visually verify that the formulas of the XLS are converting the data
correctly. I find the Import and Query options are more of a pain as the
report file CSV has the header row on the third row and so requires a number
of more steps to either get the Import or the Query to work.

Now that I am aware of the Edit/Links/Change Source option (much like how MS
Access has a link management feature) I have a hunch that the Links are a
property somewhere on the XLS file that can be modified by a VB command or
macro. I might be able to contatenate a filename based on the UserID and
update the property that stores the Source for the Links. I used to be able
to do the same thing in DBase / FoxBase where I could apply a substring such
as the UserID into a Path property to change a link to a file residing in a
user's folder based on their logon name. Again, I have a hunch it can be
done.

The macro suggestion for automation sounds good as well, and the less hands
on for the average user the better.

I think I will start to Name ranges in the XLS on the dynamic ranges in the
CSV to try and get the XLS formulas to shrink and expand as needed since the
source CSV will have variable rows.

This is getting to be an interesting journey.