473,327 Members | 2,065 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

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 3122
"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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

9
by: Joshua Beall | last post by:
Hi All, Up until now, I have been importing data from Excel spreadsheets into MySQL by exporting them to either .csv files, or tab-delimited .txt files, then using PHP scripts to insert each...
0
by: Dannie Chan | last post by:
Hi, I'm new in VB.NET, I writing a VB.NET program access MSAccess database. If recevied parts, than add received items into the MS Access database. Production software will create a xls files...
13
by: could ildg | last post by:
I want to check if a folder named "foldername" is empty. I use os.listdir(foldername)== to do this, but it will be very slow if the folder has a lot of sub-files. Is there any efficient ways to do...
4
by: Simon | last post by:
Hi, My problem is as such. I receive daily log files that arrive on our server every day with the following format: mmddyyyy.xls The problem is that these xls files are really tab delimeted...
5
by: Testguy | last post by:
Hi, I was wondering if one the smart people that frequent this group could give me a hand with a small program I am attempting to debug. I am not a highly experienced developer, but can...
0
by: Phillip Vong | last post by:
I have a simple table in SQL2k and I'm tyring to import records from a whole bunch of xls files. The SQL is set to accet SmallMoney and the XLS is set up as simple currency. When I try to import,...
1
by: mistertoony | last post by:
Hi, I am attempting to append data to an existing table in a database. What I did was create a report containing all the fields, then used analyze in Excel. I used these field headings to create...
5
by: Albert-jan Roskam | last post by:
Hi, I wrote the program below to merge all xls files in a given directory into one multisheet xls file. It uses xlwt and xlrd. The xls files I use for input are generated by Spss. When I open and...
0
by: Albert-jan Roskam | last post by:
Hi John, Thanks! Using a higher xlrd version did the trick! Regarding your other remarks: -yep, input files with multiple sheets don't work yet. I kinda repressed that ;-) Spss outputs only...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.