Tengo un archivo de datos grande (25 millones de filas) |delimitado por barras verticales (). El proveedor de datos proporciona archivos y yo ejecuto trabajos automatizados para cargar los archivos en una base de datos de Redshift y luego procesar los datos.
La siguiente es una muestra de los datos:
123|110092|ACCT|"HC Account"|"Account1||||||||||||"Mary"|||"|"||||132|"STE|504"|1253|Unspecified Account|||N||ACTV|Active||||04/30/2013|12/31/2099|||||||||||||
Hay tres conjuntos de datos de campo que he visto hasta ahora:
- Un campo de texto está entre comillas dobles (
"). Por ejemplo:"HC Account","Mary"y"|". Esto es correcto y los datos deben cargarse sin las comillas. - Algunos valores contendrán el delimitador de tubería. Por ejemplo:
"STE|504". En este caso, el campo necesariamente debe estar entre comillas dobles. Si no es así, cae en la categoría tres a continuación. - A veces sólo se proporciona una cotización inicial y no hay una cotización final. Por ejemplo:
"Account1.
TL;DR: Cualquier campo que comience con
|", debe terminar con"|. Si no es así y|"se encuentra otra, se debe escapar la primera comilla doble.
Por lo tanto, mi fila de datos debería editarse para convertirse en la siguiente después de preprocesarla en Unix/Python/otras sugerencias:
123|110092|ACCT|"HC Account"|"Account1||||||||||||"Mary"|||"|"||||132|"STE|504"|1253|Unspecified Account|||N||ACTV|Active||||04/30/2013|12/31/2099|||||||||||||
Estoy pensando en escribir un script Unix para modificar el archivo usando SED. La expresión regular que he escrito hasta ahora es:
(\|")(?!([a-zA-Z0-9]|\s|\||\/)*("\|))
Sin embargo, esto no logra hacer coincidir la cadena correctamente.
Aquí hay un enlace donde estoy probando esto:https://regexr.com/3toib
Quiero mantener el código liviano, ya que un archivo promedio tiene un tamaño de 3 a 5 GB y generalmente hay varios (10+) archivos de este tipo.
PS Redshift es un servicio de base de datos de AWS que utiliza el motor SQL de Postgre y es capaz de eliminar comillas de campos entre comillas correctamente y escapar del significado especial de una cita con \.
Además, estoy dispuesto a hacer esto en Python/cualquier otro lenguaje de secuencias de comandos dado que el código es liviano.
Respuesta1
Hay un ENORME problema con las especificaciones que ha proporcionado para los datos. Si "|"es una cadena válida, o más precisamente, una cadena entre comillas puede comenzar con una barra vertical, entonces si una cadena a la que le falta una comilla final, por ejemplo "Account1, tiene como primer campo entre comillas uno que comienza con una barra vertical, por ejemplo "|Mary", entonces no hay manera de determinaren todos los casossi "|es la cotización final |"Account1||||||||||||"|o la cotización inicial de |"|Mary"|.
Por ejemplo, usar una versión abreviada (para facilitar la lectura) ligeramente modificada de los datos, donde todas las cadenas entre comillas desde la segunda en adelante comienzan con una barra vertical y les faltan las comillas finales.
123|110092|ACCT|"HC Account"|"Account1||||||||||||"|Mary|||"|||||132|"|STE|504|1253
Se puede ver que esto se interpretará incorrectamente como
123 110092 ACCT "HC Account" "Account1||||||||||||" Mary "|||||132|" STE 504 1253
Tenga en cuenta que esto es un problema ya sea que use expresiones regulares, Python o cualquier otro lenguaje. El problema general del casopoder"resolverse", pero será complicado y requerirá el conocimiento de cuántos campos existen por fila y la estructura de datos de esos campos. (Y es posible que siempre queden casos extremos sin atender).
Dicho esto, una solución de expresiones regulares que al menos detectemayoríaLos casos en los que a una comilla doble de apertura le falta una comilla de cierre requieren un enfoque de varias pasadas, ya que la expresión regular necesita capturar todo el texto desde el inicio de cada línea hasta la primera comilla de apertura no procesada y no coincidente. (De lo contrario, como lo demuestra su expresión regular, incluso en los casos más simples se encuentran falsos positivos).
La cantidad de pases requeridos es la cantidad máxima de campos solo para comillas iniciales para cualquier fila en todo el archivo, más uno. Para finalizar el procesamiento de cada archivo es necesario detectar cuándo la expresión regular no realiza más modificaciones en el archivo.
Esta es la expresión regular más simple que funcionará en la mayoría de los casos:
Capturing Group 1 Capturing Group 2
(All previous valid fields) (Unclosed opening quote)
__________________________|_________________________ |
| || |
^((?:(?:(?!")[^|\r\n]*|"[^"\r\n]*"(?=$|\|))(?:$|\|))*+)(")
|____________| |_________________| |______|
| | |
Unquoted field OR Quoted field EOL or hypen delimiter
Úselo con esta cadena de reemplazo:
$1\\$2