469,626 Members | 997 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,626 developers. It's quick & easy.

Checking for empty [xls] files BEFORE attempting the import

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
8 2914
"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

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
"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
<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
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
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
"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

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.

Similar topics

9 posts views Thread by Joshua Beall | last post: by
reply views Thread by Dannie Chan | last post: by
13 posts views Thread by could ildg | last post: by
5 posts views Thread by Testguy | last post: by
reply views Thread by Phillip Vong | last post: by
5 posts views Thread by Albert-jan Roskam | last post: by
reply views Thread by Albert-jan Roskam | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.