Solved

ADO period in name

Posted on 2013-06-10
12
417 Views
Last Modified: 2013-07-12
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!
0
Comment
Question by:iamnamja
12 Comments
 
LVL 44

Expert Comment

by:Rainer Jeschor
ID: 39235135
Hi,
can you please post the macro as well as which Excel version?
Thanks.
0
 

Author Comment

by:iamnamja
ID: 39235167
objConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & dSource & ";" & _
        "Extended Properties=""text;HDR=Yes;FMT=Delimited(,)"";Persist Security Info=False"
objConn.Open

sqlQry = "SELECT * FROM [" & dfile & "] " & _
        "WHERE right([RIC],2) IN ('.U', '.M') "

i'm using excel 2007
0
 
LVL 14

Expert Comment

by:Zack Barresse
ID: 39235190
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
0
 

Author Comment

by:iamnamja
ID: 39235222
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.
0
 
LVL 14

Expert Comment

by:Zack Barresse
ID: 39235262
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
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Comment

by:iamnamja
ID: 39235283
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.csv"
'dFile = "test_123456.csv"

objConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & dSource & ";" & _
        "Extended Properties=""text;HDR=Yes;FMT=Delimited(,)"";Persist Security Info=False"
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
0
 
LVL 14

Accepted Solution

by:
Zack Barresse earned 500 total points
ID: 39235814
So you have two files, one with each variable names set, and if you interchange them - that being the only change - it errors out with the name containing periods?   I guess no sense for me to test it out if you ran a complete test with those conditions.

As a workaround, I would look at doing file copy, change the name, use that file, then delete when done.  You could use changes like this code, which has been slightly altered from your code to create a new file in your temporary directory, use that (parse out the period characters) and delete when done.  It works for me...

Sub foofoo()
    Dim Rcdset As New ADODB.Recordset
    Dim objConn As ADODB.Connection
    Dim sqlQry As String
    Dim dSource As String
    Dim dFile As String
    Dim i As Long

    dSource = "c:\datafolder"
    dFile = "test.20130608.20130610.csv"


    '/// Used for testing
    Dim data_ws As Worksheet
    Dim TempFile As Workbook
    Dim TempPath As String
    Dim TempName As String
    Dim NameParts() As String
    Set data_ws = ThisWorkbook.Sheets(1)
    data_ws.Cells.Clear
    TempPath = Environ("Temp")
    TempName = dFile
    NameParts = Split(dFile, ".")
    If UCase(NameParts(UBound(NameParts))) = "CSV" Then
        TempName = Left(dFile, Len(dFile) - 4) 'remove
    End If
    TempName = Replace(TempName, ".", "")
    TempName = TempName & ".CSV"
    If Dir(TempPath & "\" & TempName, vbNormal) <> vbNullString Then
        Kill TempPath & "\" & TempName
    End If
    On Error GoTo FileNotFound
    FileCopy dSource & "\" & dFile, TempPath & "\" & TempName
    On Error GoTo 0
    
    
    
    If objConn Is Nothing Then Set objConn = New ADODB.Connection
    objConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & TempPath & "\;Extended Properties=""text;HDR=Yes;FMT=Delimited(,)"";Persist Security Info=False"
    objConn.Open
    sqlQry = "SELECT * FROM [" & TempName & "] " & _
             "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

    '///
    objConn.Close
    Kill TempPath & "\" & TempName
FileNotFound:
        
        
End Sub

Open in new window


Substitute whatever you'd like.  It looks messy, but it's for testing purposes only.  You should apply a methodology like this to work with the file.  It's a new one on me that you can't have a csv (any file?) with periods in it (except the extension).

HTH
Zack
0
 

Author Comment

by:iamnamja
ID: 39238399
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?
0
 
LVL 14

Expert Comment

by:Zack Barresse
ID: 39239574
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
0
 
LVL 45

Expert Comment

by:aikimark
ID: 39247267
This is a reach, but have you tried escaping the extra periods in the file name?
Example:
dFile = "test\.20130608\.20130610.csv"

Open in new window

0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39320151
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now