Excel Macro Importing .csv - Same values Importing differently

[joel009]

I am importing 2 csv files into the same sheet. The first set of dates writes to the cell formated as General and displays as mm/dd/yyyy. The next csv with the same date is written to the cell as custom '*m/dd/yyyy'.

I can see in the process the dates held in the variable before it is written to the cell are identical - "mm/dd/yyyy " yet they are written to the cell differently and I don't understand.

Both .csv files store the values like "02/02/2013 "

here are the 2 records from the .csv files
"WILLIE","BARBER","02/02/2013 ","252.8","22 ","11.49","0 ","0","0 ","22.99","0","",,,,"Posted",,,,,

"WILLIE","BARBER","02/02/2013 ","247.81","22","11.5","0","17.25","0","0","-5.19"," FEES/DISCOUNTS","05238618","2413 ","B5641 "," ",,"767048",,,

Both are added to the sheet with the same Public Function
Public Function ReadExc(filename, rpt)
'Reads data from file
'variable is passed for filename to read from
'and rpt name
Open filename For Input As #1
While Not EOF(1)
Input #1, fname, lname, we, Amt, regHrs, regRate, otHrs, _
otRate, dblhrs, dblrate, miscAmt, MiscDesc, Invoice, Branch, _
OrdNum, Status, OrigWE, client, ssnum, extra1, extra2

ActiveCell = rpt
ActiveCell.offset(0, 1) = fname
ActiveCell.offset(0, 2) = lname
ActiveCell.offset(0, 3) = we
ActiveCell.offset(0, 4) = Amt
ActiveCell.offset(0, 5) = regHrs
ActiveCell.offset(0, 6) = regRate
ActiveCell.offset(0, 7) = otHrs
ActiveCell.offset(0, 8) = otRate
ActiveCell.offset(0, 9) = dblhrs
ActiveCell.offset(0, 10) = dblrate
ActiveCell.offset(0, 11) = miscAmt
ActiveCell.offset(0, 12) = MiscDesc
ActiveCell.offset(0, 13) = Invoice
ActiveCell.offset(0, 14) = Branch
ActiveCell.offset(0, 15) = OrdNum
ActiveCell.offset(0, 16) = Status
ActiveCell.offset(0, 17) = OrigWE
ActiveCell.offset(0, 18) = client
ActiveCell.offset(0, 19) = ssnum
ActiveCell.offset(0, 20) = extra1
ActiveCell.offset(0, 21) = extra2
ActiveCell.offset(1, 0).Activate
Wend
Close #1

End Function

Both .csv use same Public Function to write to seperate files.
Public Function WriteExc(filename, sht, Rng)
'Writes data to file
'variables for filename to write to, sheet to write from
'and starting range

Open filename For Output As #1
Sheets(sht).Select
Range(Rng).Select
While ActiveCell.offset(0, 2) > 0
Write #1, RTrim(ActiveCell), RTrim(ActiveCell.offset(0, 1)), _
ActiveCell.offset(0, 2), ActiveCell.offset(0, 3), _
ActiveCell.offset(0, 4), ActiveCell.offset(0, 5), _
ActiveCell.offset(0, 6), ActiveCell.offset(0, 7), _
ActiveCell.offset(0, 8), ActiveCell.offset(0, 9), _
ActiveCell.offset(0, 10), RTrim(ActiveCell.offset(0, 11)), _
ActiveCell.offset(0, 12), ActiveCell.offset(0, 13), _
ActiveCell.offset(0, 14), ActiveCell.offset(0, 15), _
ActiveCell.offset(0, 16), ActiveCell.offset(0, 17), _
ActiveCell.offset(0, 18), ActiveCell.offset(0, 19), _
ActiveCell.offset(0, 20)
ActiveCell.offset(1, 0).Activate
Wend
Close #1

End Function

Anyone got any ideas?
Thanks,
Joel

Joel

 

[SkipVought]

hi,

Why are you using a program to load this data???

This can be quite easly done directly from your sheet, via Data > Get External Data > From Text ... and then follow the DELIMITED using COMMA (.csv) and assign the appropriate column formats. This querytable can be refreshed whenever you get new data. NO VBA REQUIRED!!!

yet they are written to the cell differently

Exactly what do you mean by that. Examples please.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[joel009]

Hi Skip,
I am working in a payroll dept maintaining their macros from a multitude of accounts. So we are taking 2 spreadsheets, writting them to .csv files and then they compare for AR which imports them into the same sheet.

From my first post:
The first set of dates writes to the cell formated as General and displays as mm/dd/yyyy.

The next csv with the same date is written to the cell as custom '*m/dd/yyyy'.

Both .csv files store the values like "02/02/2013 " yet write them back to Excel differently.




Joel

 

[SkipVought]

HOW DIFFERENTLY???

Why Sheet > Text File > Sheet???? Does that not seem rather Rube Goldbergish?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[joel009]

Dear Skip, It is a temp position, please don't shoot the messenger.
Read my post. The information you are asking for is there.

Joel

 

[SkipVought]

Both .csv files store the values like "02/02/2013 " yet write them back to Excel differently.

????

You have not stated that on one hand the date is displayed as X, but on the other hand the date is displayed as Y.

If you change the Number Format of the Date Columns to GENERAL, then what happens to these values of concern in the FORMULA BAR?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[joel009]

From my first post:
The first set of dates writes to the cells formated as General and displays as mm/dd/yyyy.

The next csv with the same date is written to the cells as custom '*m/dd/yyyy'.

Both .csv files store the values like "02/02/2013 " yet write them back to Excel differently.

The question is why do identical values import in different formats when read from a .csv file. They are being imported to the same sheet one after the other.

When the 2nd csv file gets read into the same sheet the date format changes.

I have been in this position for 2 weeks. I can see potential for improvement in this process. There is a lot of effort made in properly formating the dates prior to sending to the csv files so they are read back the same. To me a date is a date and we could reformat after the 2 values are in the same sheet after combining on the same sheet. That being said I am following established process on a 90 temp contract.

Perhaps I need to convert the dates to Julian or something before writting the csv data. There are 30 macros set up for this as the customer is held to no standard for the data they submit and the dates are a constant problem. I have done this many times in Access but am less familiar with Excel VBA.

It appears rather Rube Goldbergish from my breif experience at my new position. But it is a process that is established and familiar to the Payroll, Accounts Receivable and the Cash Departments which all use Excel Macros to format the incoming customer reports and the client information.

I have noticed and am changing one of the standard loops used which formats multiple cells then combines rows (timecard info comes in reportng individual daily hours, we combine them into a single row reporting daily hours. m-sun or sun-sat). The process formats, for example, the name, for each row prior to combining and I move that out of the original loop which formats the name 5-7 times to after the combine so it only has to format 1 record. It runs much faster since it is formating 1 vs 5-7 lines for each final record.

I would like to figure this out. Yes or no on this problem I will check through the other macros to clarify if this column is always a date. After import into the sheet for compare I can always conditionally check if the field contains date and then format the column in Excel VBA.

something like activecell.("C2").select
if IsDate(activecell) = true then 'not sure if IsDate is available in Excel VBA
Columns(col).Select
Selection.NumberFormat = "mm/dd/yyyy;@"
end if

Back to the original question: Read the first 5 or 6 lines above.

Another question I am investigating today is why when I run Excel 2007 in Excel 2010 the undeclared variables are not being recognized. Skip - please remember I inherited these macros, so many undeclared or undefind variables. Is this standard in Excel VBA?



Joel

 

[SkipVought]

The first one is TEXT. Here's how to fix. 1) ENTER numeral 1 into an unused cell. 2) COPY that cell. 3) SELECT the cells containing the text dates. 4) Edit > Paste Special > MULTIPLY. This will coerce a conversion to date values.

In general you must change the number format BEFORE entering data. Changing the number format AFTER data is in the cell does nothing to the underlying data.

The permanent fix is to IMPORT via Data > Get external data > Text files, where you can control the column format during import, which you are unable to do via your method.

To reiterate, text values like 02/25/2013 on entry get converted to date serial numbers, which can be observed in the GENERAL number format and displayed in a variety of date formats, the underlying NUMBER remaining unchanged as number formats vary.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[joel009]

Solved my problem by removing a missing reference to a calendar.

Reading more of the posts I found what you mention above in one of your previous posts. I think this will work but will have to wait until Monday to try.

I am interested in what gets stored in the .csv files.

In Access you could create Schema to import files. Is there something like that in Excel VBA? Still digging on that but it is the weekend.

Thanks


Joel

 

[SkipVought]

Again, Excel feature (not VBA) Data > Get external data > Text files.

Once you add this QueryTable object to your sheet via the above feature, all you need do is Refresh the QueryTable to import fresh data from your text file. The "import spec" is saved with the workbook!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[joel009]

It was a long day yesterday. I figured out the Trim and RTrim weren't removing the space at the end of the displayed date and the Character left behind was Chr(160). This is a known problem in Excel but unknown to me until now. When I removed the extra character my problem was solved.

The value then stored in the .csv is #02/26/2013#.
This csv is imported first.
The 2nd csv still stores the date in the csv as "2/26/2013 " with a space (chr(32)) at the end of it yet it still imports correctly. The format must be set for the column during the first import and when the 2nd csv is imported Excel follows the same format.

Thanks for the suggestions, I always learn something here.

Now to see if I can use the suggestion about adding a query table object to the sheet.

Joel

 

[SkipVought]

Are you intending to ADD a QueryTable to your sheet via VBA? Just keep in mind that this is a ONE TIME effort. I rarely add a qt via code, FYI.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[joel009]

Not sure what I intend right now. I have 50+ systems to account for. Since the macros currently need to be system specific because the inputs are unique I would have to set up and maintain QD's for each.
The way it is set up is that for each system(time keeping) a comma delim file is created for match/compare for various purposes. The Employee Name is bumped up against our system to commonize the name so we have a Names.dat for each system which holds various names for each person( an alias list) and returns one name if it is in the .csv.

Others .csv's are used by various departments (Cash, Payroll, AR). I am maintianing these while other departments (Misc, Inv, Reporting) sometimes use them also. Some of the .csv's created are stored locally on the users C:. Most or all of the various departments use a .csv at some point or other to compare. Some are stored on a shared drive. I am trying to figure out is the ones on the shared drive are used by multiple users or departments. I think they are only used once each week by one employee or a single user. I am new to spreadsheet twirling so I'm not sure how else to handle it but off the top of my head, instead of a .csv dump it to a worksheet in the same book you are working in, use it then delete it or not. This way you could save the data you ran it against. Currently the .csv gets over written each time you run the macro. Granted we can reproduce the data, unless the macro was changed.

Is there a forum here that would give me some insight? Is there a better way?


Joel

 

[SkipVought]

Wow!

Could have a sheet for each different .csv structure to query/import into.

Could have ONE SHEET/qt and an index to assign the query parameters for the selected import.

Once you add a qt to a sheet, you can 1) turn on your macro recorder and 2) EDIT the query, 3) return the import data to the sheet 4) turn off your macro recorder and then 5) modify the code to make it as interactive or specific as you need.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}