Exporting Excel data: convert to text problem

[valeryk2000]

Hi everybody,
I am importing data from Excel spreadsheet to Access table using VBA, open ado recordset from Excel and adding the result to Access recordset.
There is one field in excel showing intervals in minutes. It looked like a number - 20, 0.5, etc. Lately they changed the representation to 0:20:00 format. When this value comes to access it looks like 1.333333E2. I need to keep 0:20:00 format, I tried to convert it to string (Cstr) - does not help. Do not know what to do. Any idea?
Val

 

[SpentGeezer]

Re: Exporting Excel data: covert to text problem

The field you are importing it into in Access needs to be string.

 

[valeryk2000]

Re: Exporting Excel data: covert to text problem

Whatever I do (format Excel column as a text) or use VB CStr function the problem remains. I tried to import it to SQL Serever - all the same story ...
I suspect the problem is Excel ...

 

[vbaInet]

Re: Exporting Excel data: covert to text problem

You would use CStr() inside the query, not in Excel.

 

[valeryk2000]

Re: Exporting Excel data: covert to text problem

Thank you.
I am not using CStr in Excel, just tried to change the datatype to text
Experiment:
Type values into 3 subsequent cells:
0:01:00
1:01:00
2:01:00

Now change datatype of the column(Format/Cell/Text) ... and ...voila!

0.000694
0.042361
0.084028

which is a numeric representation of the date. So, the conversion happens already in Excel

 

[vbaInet]

Re: Exporting Excel data: covert to text problem

Oh I didn't realise they are Time values, in that case format is as Time.

 

[valeryk2000]

Re: Exporting Excel data: covert to text problem

It is not really a time value. They present interval in minutes. The tricky part of it that sometime these field entries have 00:00:00 format, sometimes numeric (20, 30, etc.). I would like to keep these data as text but do not know how to trick the implicite conversion

 

[vbaInet]

Re: Exporting Excel data: covert to text problem

00:00:00 is a Time format. Like I mentioned, set it as a Time format to preserve it.

 

[valeryk2000]

Re: Exporting Excel data: covert to text problem

Not so simple. The tricky part is that the columns contain both "time" and "number' values:

[Alert Limit] [+/- Limit]
0:20:00 0:10:00
0:20:00 0:10:00
2 -0.5
2 -0.5
0:20:00 0:10:00
0:20:00 0:10:00
2 -0.5
2 -0.5
***************************
If we change format to time the same region would look this way:
[Alert Limit] [+/- Limit]
0:20:00 0:10:00
0:20:00 0:10:00
48:00:00 #########
48:00:00 #########
0:20:00 0:10:00
0:20:00 0:10:00
48:00:00 #########
48:00:00 #########
with negative time values presented as #########
Alas ....

 

[valeryk2000]

Re: Exporting Excel data: covert to text problem

I uploaded a sample file

 

[vbaInet]

Re: Exporting Excel data: covert to text problem

I see what you're facing now.

Import the attached.

Once you've found that successful, upload the actual data (i.e. a few records) without the Time Format applied and I'll see what's the best way to handle it.

 

[valeryk2000]

Re: Exporting Excel data: covert to text problem

Actually - you did it, you changed it to text without loosing the '00:00:00' representation - this what solves the problem. How did you do that?

 

[vbaInet]

Re: Exporting Excel data: covert to text problem

But what you uploaded had the Time format (I suggested earlier) applied to it. Let me see the raw data without the format applied and I can tell you the right steps.

 

[vbaInet]

Re: Exporting Excel data: covert to text problem

But what you uploaded had the Time format (I suggested earlier) applied to it. Let me see the raw data without the format applied and I can tell you the right steps.

Well, maybe not. I was looking at the first cell and that had the format applied but the others didn't.

Here are the steps:

1. Copy and paste into Notepad
2. Create a new sheet
2. Highlight all of Columns C and D in the new sheet, then format it as Text
3. Copy and paste from Notepad onto the new sheet.

 

[valeryk2000]

Re: Exporting Excel data: covert to text problem

The actual data was sent to me from another company as Excel file - I have no control on it (it is on the AllAvailbleColumns tab of the Sample.xls file I sent you). And I think that I can doe the same thing you've done - just paste these fields as Values and Number Formats. Let me see what's gonna happen when I import the spreadsheet into Access

 

[valeryk2000]

Re: Exporting Excel data: covert to text problem

No it does not work.

 

[vbaInet]

Re: Exporting Excel data: covert to text problem

You obviously didn't follow my instructions properly or did you not see my last post? I've just imported the spreadsheet I sent you into a db and it is formatted correctly.

After pasting the data back into Excel the field should now be aligned to the Left. If it isn't then you've obviously missed something.

 

[valeryk2000]

It works your way (thru notepad). Thank you

 

[vbaInet]

No probs!

I haven't given this much thought yet but I will look into it further because I feel there's a much better way. I will let you know if I come up with any good.

 

[valeryk2000]

In this file 0:20:00 is not realy a time but an interval - 20 min (it is never more than 60 min). So I can change it as a number using formula (see columns I and J in the attached).