Hello all,
I am DBA and not very knowledgeable in Excel.

Currently I am working with large Excel files that have to be imported
into database tables.
The problem:
All contents are formatted as General.
I have 2 types of values in same column
First scenario:
1 cell :899990
2 cell :899990AB

When I import it to database
1 cell is imported as 899990
2 cell is imported as NULL

Second scenario:
1 cell :899990AB
2 cell :899990

When I import it to database
1 cell is imported as 899990AB
2 cell is imported as NULL

I understood that If the first cell is number Excel assumes that all
others are numbers as well and vice versa.
We wrote macro that would go to each cell add empty space and format it
as text and does it for each file on the given directory. It worked
fine, but
for 23 files, 599999 lines each it took 10 days for this macro to run
and I need it to be done at least for 2 days.
Is there any other way to reformat each cell.
Thank you in advance

If you are making imports into a database, I would recommend to save the
Excel files as csv or tab delimited text files, and importing from there.
The more plain the information is when importing the better, and surely your
ETL software will be able to deal with both types of text files.

Hope this helps,
Miguel.

"sql_dba" wrote:

Hello all,
I am DBA and not very knowledgeable in Excel.

Currently I am working with large Excel files that have to be imported
into database tables.
The problem:
All contents are formatted as General.
I have 2 types of values in same column
First scenario:
1 cell :899990
2 cell :899990AB

When I import it to database
1 cell is imported as 899990
2 cell is imported as NULL

Second scenario:
1 cell :899990AB
2 cell :899990

When I import it to database
1 cell is imported as 899990AB
2 cell is imported as NULL

I understood that If the first cell is number Excel assumes that all
others are numbers as well and vice versa.
We wrote macro that would go to each cell add empty space and format it
as text and does it for each file on the given directory. It worked
fine, but
for 23 files, 599999 lines each it took 10 days for this macro to run
and I need it to be done at least for 2 days.
Is there any other way to reformat each cell.
Thank you in advance

Before we talk about solutions, we need to review limits. Excel is limited
to 65536 lines in one file. Your number of 599999 lines each points to a
problem. Are these XLS files, or some other kind of file?

"sql_dba" wrote:

Hello all,
I am DBA and not very knowledgeable in Excel.

Currently I am working with large Excel files that have to be imported
into database tables.
The problem:
All contents are formatted as General.
I have 2 types of values in same column
First scenario:
1 cell :899990
2 cell :899990AB

When I import it to database
1 cell is imported as 899990
2 cell is imported as NULL

Second scenario:
1 cell :899990AB
2 cell :899990

When I import it to database
1 cell is imported as 899990AB
2 cell is imported as NULL

I understood that If the first cell is number Excel assumes that all
others are numbers as well and vice versa.
We wrote macro that would go to each cell add empty space and format it
as text and does it for each file on the given directory. It worked
fine, but
for 23 files, 599999 lines each it took 10 days for this macro to run
and I need it to be done at least for 2 days.
Is there any other way to reformat each cell.
Thank you in advance

599999 is within the limits of XL 2007. Perhaps that's the explanation.

"Wiley" wrote:

Before we talk about solutions, we need to review limits. Excel is limited
to 65536 lines in one file. Your number of 599999 lines each points to a
problem. Are these XLS files, or some other kind of file?

"sql_dba" wrote:

Hello all,
I am DBA and not very knowledgeable in Excel.

Currently I am working with large Excel files that have to be imported
into database tables.
The problem:
All contents are formatted as General.
I have 2 types of values in same column
First scenario:
1 cell :899990
2 cell :899990AB

When I import it to database
1 cell is imported as 899990
2 cell is imported as NULL

Second scenario:
1 cell :899990AB
2 cell :899990

When I import it to database
1 cell is imported as 899990AB
2 cell is imported as NULL

I understood that If the first cell is number Excel assumes that all
others are numbers as well and vice versa.
We wrote macro that would go to each cell add empty space and format it
as text and does it for each file on the given directory. It worked
fine, but
for 23 files, 599999 lines each it took 10 days for this macro to run
and I need it to be done at least for 2 days.
Is there any other way to reformat each cell.
Thank you in advance