setting data types while linking tables to excel

[ronstar]

Hello all,

I am linking an access table to an excel spread sheet. The spread sheet in turns is linked via FTP to a csv file that is on a remote drive and that is continually being updated in real time. The spread sheet data is being refreshed every 10 minutes with new CSV data.

One of the fields in access (unfortunately the field that has the primary key) should be text field. however because sometimes the first 25 or so records in the excel sheet look like numbers the link is establishing the field in the access table as a number. As result when records appear that do not have a numerical value, then the imported field for that record becomes null.

Can someone please suggest a way in which I can force the link to consider this field as a string regardless of the data that appears in it.

Thanks
ron

 

[sparks80]

Hi,

Apparently this can be caused by the settings for the Excel Jet engine.

Open the registry editor (goto Run on the Start Menu and type "regedit.exe").

Locate the following registry key:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel\
Find "TypeGuessRows" and change it's value to 0.

This will hopefully mean that all the values in the data to set will be used to determine the datatype, rather than just the first x rows (on my copy this was set to 25 - so your guess was good).

 

[ronstar]

Hi there Sparks80

I set TypeGuessRows = 0 as you suggested. Unfortunately the program gets stuck when the value is 0. maybe there are just too many rows and it cant decide at all.

I tried TypeGuessRows = 100 as I can see that within 100 rows there is the entire variety of data (not a good solution as I never know how the data will be ordered when first pulled into access, but a way to test). It still chose Number as the data type, instead of string.

Any ideas?

cheers
Ron

 

[spikepl]

Format the offending columns all as text in Excel

 

[vbaInet]

You are using ODBC to link to the spreadsheet, set the IMEX extended property to 0. That will force TypeGuessRows to 0 and scan all rows.

 

[ronstar]

hi again,

they already are formated as text in excel, but that doesnt seem to matter. I did not chose that formating though.

The excel file is linked to a .log file and is pulling the data from it.

the reason I am using excel as an intermediary between the .log and access are:
1. access does not seem ot like .log files, and ignores them.
2. the .log file is written to continuasly, so I cant just copy it over to csv. as that will lose me access to a "live" log
3. the .log file is on a remote drive which I connect to via sftp, so I need excel to connect to the .log.

maybe you can suggest an entirely different aproach, but I have not been able ot think of one.

thanks
ron

 

[ronstar]

You are using ODBC to link to the spreadsheet, set the IMEX extended property to 0. That will force TypeGuessRows to 0 and scan all rows.

Hi,

Setting TypeGuessRows = 0 diorectly in the regedit did not help. is this s different solution? if so, I do not understand where to set IMEX extended property to 0. do I have to link the table programatically with VBA? right now it is connected using hte wazard

thanks

 

[vbaInet]

I guess you're not using ODBC then, don't worry.

Create a query based on your linked table, concatenate the empty-string to that field, i.e. Field & "", then use this query as your main source.

 

[sparks80]

Hi,

If you still can't get data in the right format I have one other thought - but it is not as elegant.

You can use VBA and an INET control to download the file from the ftp server onto the local machine. Have a look at this URL, where someone has given some example code. I haven't checked to see if it will work, but have used a similar method to download files using http in the past:

http://www.pcreview.co.uk/forums/using-vba-download-file-via-ftp-t965988.html

You could then write a procedure to delete any local copies of the file and copy the file from the ftp server.

You could then write another procedure to import the data from the file.
What is the file format of your .log file. If this contains comma separated values, then you could change the extension of the local file. This would be very straightforward using FileSystemObject (scrrun.dll).

Now you can import the data into a table from a local file which should be simpler.

If you decide to do something similar and need any advice just ask!

 

[ronstar]

Hi VBAInet,

sorry dont follow.

this is the insert statement that pulls the data from the linked table into the permanent table (which I then use). I shortened it so that it is easier to follow. the field that is I need as string and is always a number in the linked table is "deal_ref"

sqlSTR = " INSERT INTO MTActivity (......, deal_ref, ..... )" & _
" SELECT .....,MTActivityTemp.deal_ref,......" & _
" FROM MTActivityTemp" & _
" WHERE MTActivityTemp.time Is Not Null;"

DoCmd.RunSQL sqlSTR

Thanks
Ron

 

[spikepl]

#9 Note OP's using sftp. It is possible to DL via https, provided it is set up with basic authentication, otherwise it becomes a bit of a pain .)

 

[vbaInet]

SELECT MTActivityTemp.deal_ref & "", ... etc

 

[ronstar]

SELECT MTActivityTemp.deal_ref & "", ... etc

When I add & "" in the string, I get an error. Never seen that before. I must be missing something.

thanks

 

[vbaInet]

That was just an example of how to do it. It will error simply because the quotes are not completely closed.

" SELECT MTActivityTemp.deal_ref & '', ... etc

Single quotes should do.

 

[ronstar]

That was just an example of how to do it. It will error simply because the quotes are not completely closed.

" SELECT MTActivityTemp.deal_ref & '', ... etc

Single quotes should do.

|Hi,

What is the purpose of the & "

Since I dont understand, I am finding it problematic to impement this suggestiion. The string is already in quotes, and adding & " like that is causing an error

Is this what I am supposed to do?

" Select ..... , MTActivityTemp.name, " & " MTActivityTemp.deal_ref " & ", MTActivityTemp.date, ..... " & _
" From MTActivityTemp ;"

 

[vbaInet]

That implicitly converts your numeric field to string. You can also use the CStr() function in place of that but it will complain if it encounters a Null value.

" Select ..... , MTActivityTemp.name & '', MTActivityTemp.deal_ref " & ", " " MTActivityTemp.date, ..... " & _
" From MTActivityTemp ;"

 

[ronstar]

Hi

Some of the data is being lost when it is pulled into the linked table from the .log file, because it is being identified as a number, but farther down the line records appear where the data is a string.

since the field data type is being set by the first 25 records, the data from the later records is being lost.

converting it to string when copying it from the linked table to the permanent table doesnt help because the data is already lost in the process of transferring it to the linked table.

setting the TypeGuessRows to 0 did not help since there are too many records and the program gets jammed.

thanks agian
Ron

 

[vbaInet]

You need to review the process by which your records are being pulled in then. Perhaps you can explain the flow.

 

[ronstar]

Hi

That was the first thing I explained at the top of the posting.

Here is a summary for your convienience. hopefully it is clearer:
1. I have a .log file that is located on a server and to which data is written be the server continuasly (i wrote at the beginign that it is a .csv by mistake).
2. I linked excel to the .log file instead of access dierctly as access does not like to comunicate with .log files apparently. the excel file gets updated every 10 minutes.
3. I link a temporary table in access to excel and pull data from there.
4. the data in hte PK field should be a string. Hwoever, when the data gets pulled into the the linked temp table some of it is lost as the first records are mistakenly identified by access as being numbers. As a result the datatype of the field is set to number and the records that have alphanumeric data get set to null instead.
5. I use an insert statement to copy the data from the linked temp table to a permanent table with which I work.

The data is lost in the transition from excel to access. That is the point in which a fix has ot come to play.

thanks
Ron


3. I link excel to the .log

 

[ronstar]

Hi,

If you still can't get data in the right format I have one other thought - but it is not as elegant.

You can use VBA and an INET control to download the file from the ftp server onto the local machine. Have a look at this URL, where someone has given some example code. I haven't checked to see if it will work, but have used a similar method to download files using http in the past:

http://www.pcreview.co.uk/forums/using-vba-download-file-via-ftp-t965988.html

You could then write a procedure to delete any local copies of the file and copy the file from the ftp server.

You could then write another procedure to import the data from the file.
What is the file format of your .log file. If this contains comma separated values, then you could change the extension of the local file. This would be very straightforward using FileSystemObject (scrrun.dll).

Now you can import the data into a table from a local file which should be simpler.

If you decide to do something similar and need any advice just ask!

Hi Sparks,

I am trying your suggestion, but am running into difficulties downloading the file via ftp.I am reusing code I found in the site you directed me to.

Basically, I am trying to download the entire activity.log file to my local pc as you suggested and write it to a file called activity.txt

Thanks a lot
Ron

Private Function FtpDownload()
Dim vPath As String
Dim vFile As String
Dim vFTPServ As String
Dim fNum As Long
Dim vLogin As String, vPW As String

vFile = "Z:\activity.txt"
vPath = "C:\Logs"
vFTPServ = "server dns"
vLogin = "login"
vPW = "pw"


'Mounting file command for ftp.exe
fNum = FreeFile()
Open vPath & "\activity.log" For Output As #fNum
Print #1, vLogin 'use this if a UN is required
Print #1, vPW 'use this if a PASS is required
Print #1, "get " & vFile ' upload local filename to server file
Print #1, "close" ' close connection
Print #1, "quit" ' Quit ftp program
Close

Shell "ftp -n -i -g -s:" & vPath & "\activity.log " & vFTPServ, vbNormalNoFocus

End Function