How to not import the blank cell from excel to access

[haerion]

Hi all, was wondering if one of you know of a way to suppress the empty cell of an excel file when importing into access, having a hard time getting NO blank cell in the excel file.

Thanks for your help

 

[xlbo]

what is the logic to not bring these "empty" cells in ?

are they part of a larger recordset? how are you importing?

more info pls

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[haerion]

it is a database I take from a program called Versyss that I need to clear some duplicate header with excel and after import it into a table in access, but since my table have a primary key, it got me lot of error and everything when I have blank cell with the data, so im trying to get rid of these when I do my import that is made with the following macro:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "INVDFR", "D:\DFR\INVDFR.xls", True

Thanks for your help!

 

[PHV]

You may import into a temporary table in access, do some cleanup and then append the good data into INVDFR.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[haerion]

Lol, why doing complicated thing when you can do them more simple.

thanks PHV

But if you have any other idea it would be welcome since I will need to create like 5 more tables, I'm trying to stay at the lowest table qty I can.

Regards,

Haerion

 

[Alcar]

maybe a macro in excel could help... give us an idea on how the table would be structured in your excel file:

10   Frank     Alice    Brown
20   Allison   Green
30   Luke      Skywalker

or

10   Frank     Alice    Brown
20   Allison   NULL     Green
30   Luke      NULL     Skywalker

give us an idea of how the data looks like now and how you would like it to look like...



Daren J. Lahey
Programmer Analyst
FAQ183-874 contains Suggestions for Getting Quick and Appropriate Answers to your questions.

 

[haerion]

When I say empty cell, I mean empty line.

To take your example:

10 Frank Alice Brown
20 Allison Green
30 Luke Skywalker

I would have something like that but after the last data line, I got like 20 empty line that have no info in them at all. Just can't seem to delete them into access, if I delete them, excel create just the same amount I deleted.

 

[AnotherHiggins]

Just to be clear: by line you mean ROW, right? Or COLUMN? Clearly not CELLS.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[haerion]

ROW sorry

 

[Alcar]

so if I understood correctly you have something like:

10 Frank Alice Brown
20 Allison Green
empty line
empty line
empty line
empty line
empty line
30 Luke Skywalker
empty line
empty line

and so on

In this case I would use a record count and create a macro that goes row by row and deletes the empty rows until you have reached the record count...

untested code

While ActiveCell.Row < RecordCount
  'check all the fields if they are empty
  If ActiveCell.Value="" and ActiveCell.Offset(0,1).value="" Then
    'delete row
    Rows(ActiveCell.Row:ActiveCell.Row).Select
    Selection.Delete Shift:=xlUp
  end if
wend

you might want to fix the code to better suit your needs
Hope this helps!


Daren J. Lahey
Programmer Analyst
FAQ183-874 contains Suggestions for Getting Quick and Appropriate Answers to your questions.

 

[pbrodsky]

You don't even need that - if you have a field in your access table marked "required", then you won't get any records (rows) that don't have a value for that field.