iamnamja
asked on
ADO period in name
Hi,
I have a macro that needs to pull in a csv file through ADO, but the name consists of multiple periods in the name.
e.g. test.20130608.20130610.csv
I'm running into issues with the file name. Does ADO not allow for this? Or is there a way around it?
I can't change the file name because that's how one of our systems spits it out, and that would be a MUCH bigger task to change. Please look into this. Thank you!
I have a macro that needs to pull in a csv file through ADO, but the name consists of multiple periods in the name.
e.g. test.20130608.20130610.csv
I'm running into issues with the file name. Does ADO not allow for this? Or is there a way around it?
I can't change the file name because that's how one of our systems spits it out, and that would be a MUCH bigger task to change. Please look into this. Thank you!
ASKER
objConn = "Provider=Microsoft.Jet.OL
"Data Source=" & dSource & ";" & _
"Extended Properties=""text;HDR=Yes;
objConn.Open
sqlQry = "SELECT * FROM [" & dfile & "] " & _
"WHERE right([RIC],2) IN ('.U', '.M') "
i'm using excel 2007
"Data Source=" & dSource & ";" & _
"Extended Properties=""text;HDR=Yes;
objConn.Open
sqlQry = "SELECT * FROM [" & dfile & "] " & _
"WHERE right([RIC],2) IN ('.U', '.M') "
i'm using excel 2007
It's not a huge deal to rename a file. In fact you can use the 'FileCopy' method to do this, one line. I do this when working with network files, because it's always easier and faster to work with a local copy (then delete the copy to preserve original if that's what you want).
I'm assuming the filename is being passed as the 'dSource' variable? Or is it the 'dfile' variable? If you're passing just the filename, the file needs to be open. Is it?
Also, we don't know what all of your variables are. We also won't be able to tell if your SQL is built appropriately without knowing your data structure.
What line gets the error, and what is the error?
Regards,
Zack Barresse
I'm assuming the filename is being passed as the 'dSource' variable? Or is it the 'dfile' variable? If you're passing just the filename, the file needs to be open. Is it?
Also, we don't know what all of your variables are. We also won't be able to tell if your SQL is built appropriately without knowing your data structure.
What line gets the error, and what is the error?
Regards,
Zack Barresse
ASKER
Hi,
dSource is the location e.g. c:\temp
dfile is the file name e.g. test.20130608.20130610.csv
The macro runs perfectly with a filename test1223423214.csv
it only runs into an issue if there's multiple periods in the name like the example above. I did some search online, and saw others with the issue, but no real solution.
Although renaming a file or copying may be an option (worst case scenario) as the drive that the file is saved in is read only. Which means I would need to find another folder that everybody has and copy, run, delete it. And this isn't a small file so i would like to avoid doing this.
dSource is the location e.g. c:\temp
dfile is the file name e.g. test.20130608.20130610.csv
The macro runs perfectly with a filename test1223423214.csv
it only runs into an issue if there's multiple periods in the name like the example above. I did some search online, and saw others with the issue, but no real solution.
Although renaming a file or copying may be an option (worst case scenario) as the drive that the file is saved in is read only. Which means I would need to find another folder that everybody has and copy, run, delete it. And this isn't a small file so i would like to avoid doing this.
Copying a file locally isn't really a big deal. If it's disposable to delete during run-time anyway, just use your temp folder...
TempFolder = Environ("Temp")
It's really not that hard. To answer your question directly, I'm not aware of a period in a filename to give any issues. While I've used this method you have quite a bit, I can't say I've used a file with a period in its name, but I've not read any documentation which states it will cause issues. I'd think it would be something else.
Is there any chance we can look at all of your code?
Zack
TempFolder = Environ("Temp")
It's really not that hard. To answer your question directly, I'm not aware of a period in a filename to give any issues. While I've used this method you have quite a bit, I can't say I've used a file with a period in its name, but I've not read any documentation which states it will cause issues. I'd think it would be something else.
Is there any chance we can look at all of your code?
Zack
ASKER
Hi Zack,
That pretty much is all of my code, I left out the part where it tries to connect to get this data. Let me re-write below to give you the full picture:
dim Rcdset as New ADODB.Recordset, sqlQry as string, dSource as string, dFile as string
dSource = "c:\datafolder"
dFile = "test.20130608.20130610.cs
'dFile = "test_123456.csv"
objConn = "Provider=Microsoft.Jet.OL
"Data Source=" & dSource & ";" & _
"Extended Properties=""text;HDR=Yes;
objConn.Open
sqlQry = "SELECT * FROM [" & dfile & "] " & _
"WHERE right([RIC],2) IN ('.U', '.M') "
Rcdset.Open sqlQry, objConn
For i = 0 To Rcdset.Fields.Count - 2
data_ws.Cells(1, i + 1).Value = Rcdset.Fields(i).Name
Next i
data_ws.Cells(2, 1).CopyFromRecordset Rcdset
*Note instead of using the first filename, if I use the commented filename, it runs perfectly. It fails at this line: Rcdset.Open sqlQry, objConn
That pretty much is all of my code, I left out the part where it tries to connect to get this data. Let me re-write below to give you the full picture:
dim Rcdset as New ADODB.Recordset, sqlQry as string, dSource as string, dFile as string
dSource = "c:\datafolder"
dFile = "test.20130608.20130610.cs
'dFile = "test_123456.csv"
objConn = "Provider=Microsoft.Jet.OL
"Data Source=" & dSource & ";" & _
"Extended Properties=""text;HDR=Yes;
objConn.Open
sqlQry = "SELECT * FROM [" & dfile & "] " & _
"WHERE right([RIC],2) IN ('.U', '.M') "
Rcdset.Open sqlQry, objConn
For i = 0 To Rcdset.Fields.Count - 2
data_ws.Cells(1, i + 1).Value = Rcdset.Fields(i).Name
Next i
data_ws.Cells(2, 1).CopyFromRecordset Rcdset
*Note instead of using the first filename, if I use the commented filename, it runs perfectly. It fails at this line: Rcdset.Open sqlQry, objConn
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Zack,
I do have a working version that copies the file and removes the periods as you've suggested above. Although this does do its job, I would like to see if there's any way I can go about doing this without having to do this additional step.
I wonder if this is a limitation on ADO where it looks for the first period to assume that's the file extension?
I do have a working version that copies the file and removes the periods as you've suggested above. Although this does do its job, I would like to see if there's any way I can go about doing this without having to do this additional step.
I wonder if this is a limitation on ADO where it looks for the first period to assume that's the file extension?
Honestly, I've never come across that before. I searched for quite some time for relevant information, common errors and similar problems, and have found nothing on that issue. Strange indeed. At least there is a workaround.
Zack
Zack
This is a reach, but have you tried escaping the extra periods in the file name?
Example:
Example:
dFile = "test\.20130608\.20130610.csv"
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
can you please post the macro as well as which Excel version?
Thanks.