如何获取巨大的管道分隔文件中的最大字段长度 [英] How to obtain max length of fields in huge pipe delimited file

问题描述

我有一个管道分隔文件太大，无法在Excel中打开。我试图使用SSMS中的导入向导将此文件导入MSSQL。

I have a pipe delimited file that is too large to open in Excel. I'm trying to import this file into MSSQL using the import wizard in SSMS.

通常，当我这样做时，我在Excel中打开文件，并使用数组函数= MAX（LEN（An：Annnn））来获取每列的最大长度。然后我使用它来指定我的表中每个字段的大小。

Normally when I do this, I open the file in Excel and use an array function =MAX(LEN(An:Annnn)) to get the max length of each column. Then I use that to specify the size of each field in my table.

此文件太大，无法在Excel中打开，SQL不会检查所有数据以提供准确的建议（我认为这是一个疯狂的小样本，如200条记录）。

This file is too large to open in Excel and SQL doesn't check all of the data to give an accurate suggestion (I think it's a crazy small sample like 200 records).

任何人都有解决方案（我不反对在Linux做某事，特别是如果它是免费的）。

Anyone have a solution to this (I'm not opposed to doing something in Linux especially if it's free).

感谢您提供任何帮助。

Thanks in advance for any help.

推荐答案

当我将文本数据导入数据库时​​，通常我首先将数据读入登台表， -enough字段字段（例如 varchar（8000））。

When I import text data into a database, typically I first read the data into a staging table where are the columns are long-enough character fields (say varchar(8000)).

然后，我从暂存表装入最终表：

Then, I load from the staging table into the final table:

create table RealTable (
    RealTableId int identity(1, 1) primary key,
    Column1 int,
    Column2 datetime,
    Column3 varchar(12),
    . . .
);

insert into RealTable(<all columns but id>)
    select (case when column1 not like '[^0-9]' then cast(column1 as int) end),
           (case when isdate(column2) = 1 then cast(column2 as datetime),
           . . .

b $ b

我发现在数据库中调试类型问题比在插入数据库时​​容易多了。

I find it much easier to debug type issues inside the database rather than when inserting into the database.

这篇关于如何获取巨大的管道分隔文件中的最大字段长度的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！