por - macro para convertir archivo txt a excel

Quiere que VBA en Excel lea CSV de gran tamaño y cree un archivo de salida de un pequeño subconjunto del CSV

Question

Tengo un archivo csv de 1,2 millones de registros de texto. Los campos alfanuméricos están entre comillas, la fecha / hora o los campos numéricos no.

Por ejemplo, "Fred", "Smith", 01/07 / 1967,2, "7, The High Street", "Anytown", "Anycounty", "LS1 7AA".

Lo que quiero hacer es escribir algunos VBA en Excel (más o menos la única herramienta disponible para mí que soy razonablemente hábil en el uso de) que lee el registro CSV por registro, realiza un control (como ocurre en el último campo, el código postal) y luego genera un pequeño subconjunto de los registros de 1.2 m en un nuevo archivo de salida.

Entiendo cómo abrir los dos archivos, leer el registro, hacer lo que tengo que hacer con los datos y escribirlos (solo mostraré el registro de entrada con un prefijo que indica un tipo de excepción)

Lo que no sé es cómo analizar correctamente el CSV en VBA. No puedo hacer un simple escaneo de texto y buscar comas ya que el texto a veces tiene comas (de ahí que los campos de texto estén delimitados por texto)

¿Hay algún comando fantástico que me permita obtener rápidamente los datos del enésimo campo de mi registro?

Lo que quiero es s_work = field (s_input_record, 5) donde 5 es el número de campo en mi CSV ....

Muchas gracias, C

Answer 1

Esto no responde directamente a su pregunta, pero grep (o uno de los equivalentes de Windows) realmente brillaría para esto, por ejemplo,

grep -e <regex_filter> foo.csv > bar.csv

Answer 2

Qué tal VBScript, aunque esto también funcionaría en Excel:

Set cn = CreateObject("ADODB.Connection")

''Note HDR=Yes, that is, first row contains field names ''
''and FMT delimted, ie CSV ''

strCon="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:/Docs/;" _
& "Extended Properties=""text;HDR=Yes;FMT=Delimited"";"

cn.open strcon

''You would not need delimiters ('''') if last field is numeric: ''    
strSQL="SELECT FieldName1, FieldName2 INTO New.csv FROM Old.csv " _
& " WHERE LastFieldName=''SomeTextValue''"

''Creates new csv file
cn.Execute strSQL

Answer 3

Sugiero que eche un vistazo a la biblioteca Regular Expression (debería verla en "Herramientas ... Referencias" como "Microsoft VBScript Regular Expressions 5.5" o algo muy similar.

Hay ejemplos tanto del Reg Exp como de un carácter bastante completo carácter por carácter en esta ubicación: http://www.xbeat.net/vbspeed/c_ParseCSV.php . Tenga en cuenta que la versión de Regexp es más corta.

Que te diviertas...

Answer 4

Cualquier cosa que pueda hacer una fila a la vez con vba en excel, puede hacerlo en acceso con vba; además de mucho más porque es una base de datos en lugar de una hoja de cálculo. ¿El acceso no está disponible para ti?

Es mucho más fácil tratar con tablas lógicas, registros y campos que con hojas de cálculo lógicas, filas y columnas.

Para la entrada, ¿por qué no funciona el "/ Datos / Importar Datos Externos / Texto / csv"? ¿La entrada no es realmente portable csv?

Answer 5

Mire la declaración Input # en la ayuda de Excel

El uso de la muestra sería:

Input #fnInput, s_Forename, s_Surname, dt_DOB, i_Something, s_Street, s_Town, s_County, s_Postcode

y luego use la instrucción Write # para volver a escribir registros coincidentes

El único problema podría ser que el formato de fecha en el resultado finalice como # 1967-07-01 #, pero este formato no es ambiguo, a diferencia del 01/07/1967, que representaría el 1 de julio en el Reino Unido y el 7 de enero en los EE. UU. Si necesita conservar el formato de la fecha, escríbala como una cadena:

s_DOB = Format(dt_DOB, "dd/mm/yyyy")

Answer 6

Utilicé la siguiente derivada del código proporcionado anteriormente para abrir con éxito un archivo csv arbitrario de VBA en Excel.

Opción explícita
Cn público como conexión
Public Sub Doit ()
Dim Strcon como cadena
Dim strsql como cadena
Dim rs As Recordset

Establecer cn = CreateObject ("ADODB.Connection")

strcon = "Proveedor = Microsoft.Jet.OLEDB.4.0; Fuente de datos = C: / bin / HomePlanet /;" _
& "Extended Properties =" "text; HDR = Yes; FMT = Delimited" ";"

cn.Open strcon

strsql = "SELECCIONAR * FROM astuname.csv"
Set rs = Nuevo ADODB.Recordset
rs.Open strsql, cn
La pausa de DoEvents aquí para inspeccionar objetos y propiedades rs.Close
End Sub

El rs (recordset) tiene una colección de campos, con una propiedad Count. Cada campo como una propiedad Tipo.

Puede hacer referencia a los campos por número de secuencia ...

Debug.Print rs.Fields (rs.Fields.Count - 1) .Type

¿Es esto suficiente?

Si no, publique las primeras filas del archivo de entrada y lo tomaré el resto del camino.

Answer 7

El siguiente código debería hacer el truco. No tengo Excel delante de mí, así que no lo he probado, pero el concepto es sólido.

Si esto termina siendo demasiado lento, podemos buscar formas de mejorar la eficiencia.

Sub SelectSomeRecords()
    Dim testLine As String

    Open inputFileName For Input As #1
    Open outputFileName For Output As #2

    While Not EOF(1)
        Line Input #1, testLine
        If RecordIsInteresting(testLine) Then
            Print #2, testLine
        End If
    Wend

    Close #1
    Close #2
End Sub

Function RecordIsInteresting(recordLine As String) As Boolean
    Dim lineItems(1 to 8) As String

    GetRecordItems(lineItems(), recordLine)

    ''''// do your custom checking here:
    RecordIsInteresting = lineItems(8) = "LS1 7AA"
End Function

Sub GetRecordItems(items() As String, recordLine as String)
    Dim finishString as Boolean
    Dim itemString as String
    Dim itemIndex as Integer
    Dim charIndex as Long
    Dim inQuote as Boolean
    Dim testChar as String

    inQuote = False
    charIndex = 1
    itemIndex = 1
    itemString = ""
    finishString = False

    While charIndex <= Len(recordLine)
        testChar = Mid$(recordLine, charIndex, 1)

        finishString = False

        If inQuote Then
            If testChar = Chr$(34) Then
                inQuote = False
                finishString = True
                charIndex = charIndex + 1 ''''// ignore the next comma
            Else
                itemString = itemString + testChar
            End If
        Else
            If testChar = Chr$(34) Then
                inQuote = True
            ElseIf testChar = "," Then
                finishString = True
            Else
                itemString = itemString + testChar
            End If
        End If

        If finishString Then
            items(itemIndex) = itemString
            itemString = ""
            itemIndex = itemIndex + 1
        End If

        charIndex = charIndex + 1
    Wend
End Sub