将多个csv中的数据加载到mysql中

a11xaf1n  于 2021-06-20  发布在  Mysql
关注(0)|答案(0)|浏览(270)

我正在尝试将多个csv文件加载到mysql中,以创建一个包含这些多个文件的表。我在mysql中加载了一个名为symbols的表,其中有一列名为ticker,所有的行值都表示a、b、c等符号。我想循环这些行来创建一个路径 /path/A.csv 这样我就可以从所有这些文件中读取数据来创建新的表all\ U tickers。
之前,我在sql server中使用以下代码执行了此操作:

CREATE TABLE dbo.all_tickers
(
     ticker_symbol varchar(32) NOT NULL,
     [Date] datetime2(7) NOT NULL,
     [Open] float NULL,
     High float NULL,
     Low float NULL,
     [close] float NULL,
     Volume float NULL,

     PRIMARY KEY (ticker_symbol, [Date])
);
GO

DECLARE @Path nvarchar(255) = 'D:\ASX';
DECLARE @SQL nvarchar(MAX);

CREATE TABLE #ticker_staging
(
     [Date] datetime2(7) NOT NULL,
     [Open] float NULL,
     High float NULL,
     Low float NULL,
     [close] float NULL,
     Volume float NULL
);

SET @SQL = (SELECT N'
TRUNCATE TABLE #ticker_staging;
BULK INSERT #ticker_staging
    FROM ''' + @Path + N'\' + ticker + N'.csv''
    WITH
    (
    FIRSTROW = 2,
    FIELDTERMINATOR = '','',  --CSV field delimiter
    ROWTERMINATOR = ''\n'',   --Use to shift the control to next row
    TABLOCK
    );
INSERT INTO dbo.all_tickers WITH (TABLOCKX)(
  ticker_symbol,
  [Date],
  [Open],
  High,
  Low,
  [close],
  Volume
) 
SELECT
  ''' + ticker + N''',
  [Date],
  [Open],
  High,
  Low,
  [close],
  Volume
FROM #ticker_staging;'
FROM dbo.symbols
FOR XML PATH(''), TYPE).value('.',N'nvarchar(MAX)');
EXECUTE sp_executesql @SQL;
GO

为了在mysql中复制结果,我将上述代码更改为:

CREATE TABLE dbo.all_tickers
(
     ticker_symbol varchar(32) NOT NULL,
     Date datetime NOT NULL,
     Open float NULL,
     High float NULL,
     Low float NULL,
     close float NULL,
     Volume float NULL,

     PRIMARY KEY (ticker_symbol, Date)
);

set @Path = 'D:\ASX';
set @SQL = nvarchar(MAX);

CREATE TABLE #ticker_staging 
( 
    Date1 datetime NOT NULL,
    Open float NULL,
    High float NULL,
    Low float NULL,
    close float NULL,
    Volume float NULL
);

SET @SQL = (SELECT N'
TRUNCATE TABLE #ticker_staging;
LOAD DATA INFILE #ticker_staging
    FROM ''' + @Path + N'\' + ticker + N'.csv''
    WITH
    (
    FIRSTROW = 2,
    FIELDTERMINATOR = '','',  #CSV field delimiter
    ROWTERMINATOR = ''\n'',   #Use to shift the control to next row
    TABLOCK
    );
INSERT INTO dbo.all_tickers WITH (TABLOCKX)(
  ticker_symbol,
  [Date],
  [Open],
  High,
  Low,
  [close],
  Volume
) 
SELECT
  ''' + ticker + N''',
  [Date],
  [Open],
  High,
  Low,
  [close],
  Volume
FROM #ticker_staging;'
FROM dbo.SCRIP
FOR XML PATH(''), TYPE).value('.',N'nvarchar(MAX)');
EXECUTE sp_executesql @SQL;

我在处理这个问题上有困难 WITH sql server中用于将其转换为mysql的子句。
因为我是mysql的新手,所以我认为这是主要的问题。如果代码中有其他问题,请建议。

暂无答案!

目前还没有任何答案,快来回答吧!

相关问题