By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,635 Members | 2,243 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,635 IT Pros & Developers. It's quick & easy.

Checking for empty [xls] files BEFORE attempting the import

P: n/a
Hi, anyone got any thoughts on this problem?

I have sales reps. that remotely send their data to an ftp server.
The office downloads all files, the code creates an empty file, then
downloads the data to it, then moves on to the next rep:

----------
[For each....]

conTARGETVisits = FTPLocation & "RepCallPlan" & Format(MyRepCode, "00")
& ".xls"

Set objFTP = New InetTransferLib.ftp
With objFTP
.UseProxy = True
.FTPURL = conTARGETVisits
.DestinationFile = WhereTo & "RepCallPlan" & Format(MyRepCode,
"00") & ".xls"
.PromptWithCommonDialog = False
If .FileExists Then .OverwriteTarget = True
If Not .IsConnected Then .DialDefaultNumber
.ConnectToFTPHost FTPUserName, FTPPassword
.WriteFTPDataToFile
End With
----------

The data is an update, not new data, so what I currently do is use sql
to delete the old rows, then import the new data. My problem is that if
the file is empty then there is nothing to import (I use error trapping
for Err.Number "3274" to skip that file)

I can't import the data first then delete the old rows, various
reasons, not least of which is that there will be numerous pairs of
identical data, or those that have changed there no way of knowing what
is the correct one to keep.

The import code is:
----------
stSQL = "SELECT StaffNumber FROM tblStaff WHERE (StaffRole=""Area Sales
Manager"");" 'only list sales reps.

Set rst = CurrentDb.OpenRecordset(stSQL)

Do While Not rst.EOF

DoCmd.TransferSpreadsheet acImport, , "tblCustomersCallPlanDayWeek", _
FileLocation & "RepCallPlan" & Format(rst("StaffNumber"), "00") &
".xls", True

rst.MoveNext

Loop
----------

What I need to do is see if each [xls] file has data, if yes delete the
old data then import the new rows, but I can't get my head around
checking for the data without attempting the import, which of course if
successful gives me the duplicate data problem...!

Any help would, of course, be appreciated.

Thank you

Jon

Aug 19 '06 #1
Share this Question
Share on Google+
8 Replies


P: n/a
"J-P-W" <jo******@gmail.comwrote in
news:11**********************@74g2000cwt.googlegro ups.com:
Hi, anyone got any thoughts on this problem?

I have sales reps. that remotely send their data to an ftp
server. The office downloads all files, the code creates an
empty file, then downloads the data to it, then moves on to
the next rep:

----------

What I need to do is see if each [xls] file has data, if yes
delete the old data then import the new rows, but I can't get
my head around checking for the data without attempting the
import, which of course if successful gives me the duplicate
data problem...!

Any help would, of course, be appreciated.
Import to a temporary table. you can test that table for no
records, then proceed to clear the permanent table and move the
records. Once done, clear the temp table so it's ready for the
next import session.

Thank you

Jon



--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

Aug 19 '06 #2

P: n/a

Bob Quintal wrote:
"J-P-W" <jo******@gmail.comwrote in
news:11**********************@74g2000cwt.googlegro ups.com:
Hi, anyone got any thoughts on this problem?

I have sales reps. that remotely send their data to an ftp
server. The office downloads all files, the code creates an
empty file, then downloads the data to it, then moves on to
the next rep:

----------

What I need to do is see if each [xls] file has data, if yes
delete the old data then import the new rows, but I can't get
my head around checking for the data without attempting the
import, which of course if successful gives me the duplicate
data problem...!

Any help would, of course, be appreciated.
Import to a temporary table. you can test that table for no
records, then proceed to clear the permanent table and move the
records. Once done, clear the temp table so it's ready for the
next import session.

Thank you

Jon

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com
Yep I guess that'd work, I'd like to find out if the file is empty
first though, because then I could skip straight to the next one, maybe
someone will suggest some way of doing that, thank you

Jon

Aug 19 '06 #3

P: n/a
"J-P-W" <jo******@gmail.comwrote in
news:11*********************@m79g2000cwm.googlegro ups.com:
>
Bob Quintal wrote:
>"J-P-W" <jo******@gmail.comwrote in
news:11**********************@74g2000cwt.googlegr oups.com:
Hi, anyone got any thoughts on this problem?

I have sales reps. that remotely send their data to an ftp
server. The office downloads all files, the code creates an
empty file, then downloads the data to it, then moves on to
the next rep:

----------

What I need to do is see if each [xls] file has data, if
yes delete the old data then import the new rows, but I
can't get my head around checking for the data without
attempting the import, which of course if successful gives
me the duplicate data problem...!

Any help would, of course, be appreciated.
Import to a temporary table. you can test that table for no
records, then proceed to clear the permanent table and move
the records. Once done, clear the temp table so it's ready
for the next import session.

Thank you

Jon


--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

Yep I guess that'd work, I'd like to find out if the file is
empty first though, because then I could skip straight to the
next one, maybe someone will suggest some way of doing that,
thank you

Jon
I can tell you the other way. Importing is much faster.

What you do is open the Excel file under automation and read a
cell at a specific location. If it's empty, and you know that
there should be data there, you have determined that the
spreadsheet is empty.

to open an Excel file via code see
http://www.mvps.org/access/modules/mdl0006.htm

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

Aug 19 '06 #4

P: n/a
<Snip>
Hi Bob,

Thanks for your help with this, agreed that would be much slower!
Thanks back to plan A then, regards

Jon

Aug 19 '06 #5

P: n/a
Bob Quintal <rq******@sPAmpatico.cawrote in
news:Xn**********************@66.150.105.47:
I can tell you the other way. Importing is much faster.

What you do is open the Excel file under automation and read a
cell at a specific location. If it's empty, and you know that
there should be data there, you have determined that the
spreadsheet is empty.
What I've done is create a link to the spreadsheet. I then check a
particular field to see if it's equal to a zero-length string. If
so, the row is empty. If the field name is invalid, it means the
worksheet doesn't even have column headings.

I've also done this to eliminate empty rows after an import of a
spreadsheet where for some reasons rows that are empty but have been
formatted get imported.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Aug 20 '06 #6

P: n/a
David W. Fenton wrote:
Bob Quintal <rq******@sPAmpatico.cawrote in
news:Xn**********************@66.150.105.47:
I can tell you the other way. Importing is much faster.

What you do is open the Excel file under automation and read a
cell at a specific location. If it's empty, and you know that
there should be data there, you have determined that the
spreadsheet is empty.

What I've done is create a link to the spreadsheet. I then check a
particular field to see if it's equal to a zero-length string. If
so, the row is empty. If the field name is invalid, it means the
worksheet doesn't even have column headings.

I've also done this to eliminate empty rows after an import of a
spreadsheet where for some reasons rows that are empty but have been
formatted get imported.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Hi David, do you mean table linked to the xls file, if so - location of
the files change (they're in dated folders) so I can't use that!!! If
the checking is done be code then that would work... have you an
example please?

Many thanks

Jon

Aug 20 '06 #7

P: n/a
"J-P-W" <jo******@gmail.comwrote in
news:11*********************@i3g2000cwc.googlegrou ps.com:
David W. Fenton wrote:
>What I've done is create a link to the spreadsheet. I then check
a particular field to see if it's equal to a zero-length string.
If so, the row is empty. If the field name is invalid, it means
the worksheet doesn't even have column headings.

I've also done this to eliminate empty rows after an import of a
spreadsheet where for some reasons rows that are empty but have
been formatted get imported.

Hi David, do you mean table linked to the xls file, if so -
location of the files change (they're in dated folders) so I can't
use that!!!
I have done it so that the link is created on the fly, rather than
being persistent.
If
the checking is done be code then that would work... have you an
example please?
Once it's linked, it's just like working with any other table.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Aug 21 '06 #8

P: n/a

David W. Fenton wrote:
"J-P-W" <jo******@gmail.comwrote in
news:11*********************@i3g2000cwc.googlegrou ps.com:
David W. Fenton wrote:
What I've done is create a link to the spreadsheet. I then check
a particular field to see if it's equal to a zero-length string.
If so, the row is empty. If the field name is invalid, it means
the worksheet doesn't even have column headings.

I've also done this to eliminate empty rows after an import of a
spreadsheet where for some reasons rows that are empty but have
been formatted get imported.
Hi David, do you mean table linked to the xls file, if so -
location of the files change (they're in dated folders) so I can't
use that!!!

I have done it so that the link is created on the fly, rather than
being persistent.
If
the checking is done be code then that would work... have you an
example please?

Once it's linked, it's just like working with any other table.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
I decided to go with the import to temp file, check for records, if
there are some delete original rows, copy temp data, and was happily
suporised out how well it worked!!!

Thank both for help.

Jon

Aug 22 '06 #9

This discussion thread is closed

Replies have been disabled for this discussion.