**Inline Commented following
"Harlan Grove" wrote:
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.
**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 users 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 users
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 softwareI 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 acceptnot an XLS).
If this is easier outside of Excel, then Im 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?
|