Problems whenimporting data from Excel

[mondoray]

Guys,

I have a db that relies on the import of data from and Excel spreadsheet. Unfortunately some of the spreadsheet cells have merged data across two rows. When the Excel data is imported into the table the data from the first row of the merged cells is populated and not the second row.

Is there a way to fix the problem?

Many thanks

mondoray

 

[Bob44TheCat]

Hi Mondoray,
How is the data merged in the spreadsheet? e.g. Do you have two cells merged using the Format > Cells > Alignment option, or do you have data in one cell which wraps onto two lines? If it's the latter, it could be that you've got a carriage return (or some other hidden character) which is being identified by the import process as the end of that piece of data.
Cheers,
Bob.

 

[GingerR]

Hi Mondoray--Are you talking about Excel ROWS, or COLUMNS? I just wanted to be sure, since you used the word "across" I wondered if you mean 'columns' instead.

If indeed Rows: You'll have to figure out some way to re-format the Excel sheet before importing. How many records are there? How often do you have to do this importing?

 

[mondoray]

Guys,

Thks for replies. The data is merged across rows. The spreadsheet is a production plan. As some of the work is across a weekend or a two day period the data has been merged across those two days (rows).

The data is for a 45 day work plan. The merged data occurs occasionally overthat period.

mondoray

 

[GingerR]

without seeing the excel sheet it's hard to help without further details.
Is there something that distinguishes the "Second" row from the first, all the time, whenever that situation happens?

What is happening at this point? When you just manually import the data into a NEW table, does all of the excel data come in? It should, it just might not be how you want it in it's final format. then maybe that's what you'd have to do--just import it in, in whatever format it comes in as, then write queries to parse out the different data from the different rows and append it all into a final table that is in the format you want. know what i mean?

 

[mondoray]

GingerR,

The spreadsheet has the rows in days and date. The columns are the items eg. Truck1

Truck1 Truck2

Saturday Work Service
Sunday today

Because "Work today" is in merged cells across two rows during import the data registerd is the data in the "work" row. The data in the "today" row is not imported.

mondoray

 

[GingerR]

i'm having a hard time duplicating this.

in excel i made a spreadsheet with this data:
column: A B C
Row1: Saturday Work Today Steve
Row2: Sunday Bill

I made the cells B1 and B2 merged, so there is no a real cell at "B2".

I imported into Access, and both rows of data imported fine, with what would be considered B2 being blank, and B1 simply having the words "Work Today". I must be missing something. As Bob44 asked, are the cells "MERGED" in Excel, or are there carriage returns, or something else?

 

[mondoray]

GingerR,

This question was posted on behalf of a another member of staff.

He advises the the data is just merged without a cr. I have passed on yours and Bob44's suggestions.

Thks for all your help.

mondoray