Query para importar CSV delimitado para Excel

weivisson

15-09-2016 14:23:25

Prezados,
consigo rodar uma query para importar arquivo .csv para o Excel, porém não consigo separar nas colunas. O delimitador é ";", já pesquisei muito, apliquei as soluções que pude encontrar e mesmo assim não resolve.
Segue código para avaliação e sugestões:

Public Sub QueryTextFile()
Dim Recordset As ADODB.Recordset
Dim ConnectionString As String
Dim currentDataFilePath As String
Dim currentDataFileName As String
Dim nextRow As Integer

currentDataFilePath = "C:\Users\raineto\Documents\DBM\"
currentDataFileName = "FULL.csv"
ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & currentDataFilePath & ";" & "Extended Properties=""Text;HDR=No;FMT=Delimited(;)"";"

Const SQL As String = "SELECT * FROM FULL.csv;"
Set Recordset = New ADODB.Recordset
Call Recordset.Open(SQL, ConnectionString, CursorTypeEnum.adOpenForwardOnly, LockTypeEnum.adLockReadOnly, CommandTypeEnum.adCmdText)
Call Plan1.Range("A1").CopyFromRecordset(Recordset)
Recordset.Close
Set Recordset = Nothing
End Sub
Grato,
Weivisson

webmaster

15-09-2016 21:51:36

Weivisson,

Pergunta: porque nao usa o mecanismo de importacao do Excel? Nao resolve?

Uma referencia para comecar:

Código: Selecionar todos

Sub ImportTextFile()

       Dim DestBook As Workbook, SourceBook As Workbook
       Dim DestCell As Range
       Dim RetVal As Boolean

       ' Turn off screen updating.
       Application.ScreenUpdating = False

       ' Set object variables for the active book and active cell.
       Set DestBook = ActiveWorkbook
       Set DestCell = ActiveCell

       ' Show the Open dialog box.
       RetVal = Application.Dialogs(xlDialogOpen).Show("*.txt")

       ' If Retval is false (Open dialog canceled), exit the procedure.
       If RetVal = False Then Exit Sub

       ' Set an object variable for the workbook containing the text file.
       Set SourceBook = ActiveWorkbook

       ' Copy the contents of the entire sheet containing the text file.
       Range(Range("A1"), Range("A1").SpecialCells(xlLastCell)).Copy

       ' Activate the destination workbook and paste special the values
       ' from the text file.
       DestBook.Activate
       DestCell.PasteSpecial Paste:=xlValues

       ' Close the book containing the text file.
       SourceBook.Close False

   End Sub
Link: https://support.microsoft.com/en-us/kb/133198