I have a system where I can only read 50 column's worth of data from a table at a time. The table has 150 columns that I need to read. The table has 1 million lines.
我有一个系统,我一次只能从一个表中读取50列的数据。该表有150列我需要阅读。该表有100万行。
What I have been doing so far is to read the 1st 50 columns and write all 1 millions lines to a text file.
到目前为止,我一直在做的是阅读前50列并将所有1百万行写入文本文件。
What I need to do next, is read next block of 50 columns and append lines 1 to 1 million into the existing text file that I have already written to.
我接下来需要做的是读取下一个50列的块,并将1到100万行附加到我已经写入的现有文本文件中。
This is where I become stuck. I'm not sure how I can write to a specific line in the text file. I was hoping not to have to loop each line in the existing text file to get to the row I need to append to. Is there a way to specify the line I need to append to without having to loop the text file to find the correct row?
这就是我陷入困境的地方。我不确定如何写入文本文件中的特定行。我希望不必循环现有文本文件中的每一行来到我需要追加的行。有没有办法指定我需要追加的行而不必循环文本文件来找到正确的行?
1 个解决方案
#1
0
I would use SQL OLEDB and simply output data using RecordSet. Might take some time but should work. Try using LIMIT and FETCH in a loop in case the result won't load in a single go. Alternatively add a loop to the SQL statement and fetch records higher lower then a specific row ID (if your records ID is a numeric not a hash) e.g. WHERE ID > [OLD_ID] and ID <= [OLD_ID] + 100
我会使用SQL OLEDB并使用RecordSet简单地输出数据。可能需要一些时间,但应该工作。尝试在循环中使用LIMIT和FETCH,以防结果不能一次加载。或者,在SQL语句中添加一个循环,并获取比特定行ID更低的记录(如果您的记录ID是数字而不是哈希),例如WHERE ID> [OLD_ID]和ID <= [OLD_ID] + 100
Using SQL OLEDB and RecordSet
Dim fileName As String, textData As String, textRow As String, fileNo As Integer
fileNo = FreeFile
Open fileName For Output As #fileNo
Set rs = CreateObject("ADODB.Recordset")
sConn = "OLEDB;Provider=SQLOLEDB;Data Source=" & _
serverInstance & ";Initial Catalog=" & database & _
";User ID=" & userId & ";Password=" & password & ";"
strSQL = "SELECT * FROM [table_name] "
rs.Open strSQL, strcon, 3, 3
rs.MoveFirst
Do
textData = ""
textData = textData & ";" & rs("Col1")
textData = textData & ";" & rs("Col2")
textData = textData & ";" & rs("Col3") & vbNewLine
Print #fileNo, textData
rs.MoveNext
Loop Until rs.EOF
Close #fileNo
#1
0
I would use SQL OLEDB and simply output data using RecordSet. Might take some time but should work. Try using LIMIT and FETCH in a loop in case the result won't load in a single go. Alternatively add a loop to the SQL statement and fetch records higher lower then a specific row ID (if your records ID is a numeric not a hash) e.g. WHERE ID > [OLD_ID] and ID <= [OLD_ID] + 100
我会使用SQL OLEDB并使用RecordSet简单地输出数据。可能需要一些时间,但应该工作。尝试在循环中使用LIMIT和FETCH,以防结果不能一次加载。或者,在SQL语句中添加一个循环,并获取比特定行ID更低的记录(如果您的记录ID是数字而不是哈希),例如WHERE ID> [OLD_ID]和ID <= [OLD_ID] + 100
Using SQL OLEDB and RecordSet
Dim fileName As String, textData As String, textRow As String, fileNo As Integer
fileNo = FreeFile
Open fileName For Output As #fileNo
Set rs = CreateObject("ADODB.Recordset")
sConn = "OLEDB;Provider=SQLOLEDB;Data Source=" & _
serverInstance & ";Initial Catalog=" & database & _
";User ID=" & userId & ";Password=" & password & ";"
strSQL = "SELECT * FROM [table_name] "
rs.Open strSQL, strcon, 3, 3
rs.MoveFirst
Do
textData = ""
textData = textData & ";" & rs("Col1")
textData = textData & ";" & rs("Col2")
textData = textData & ";" & rs("Col3") & vbNewLine
Print #fileNo, textData
rs.MoveNext
Loop Until rs.EOF
Close #fileNo