vba - Excel is freezing when importing large CSV -

-

i have large csv file, 60 columns , 50k rows. (i can't show because in intranet.)

i wrote vba code this:

columns("d:bf").select selection.clearcontents *code import csv file d1* lastrow = cells(rows.count, "d").end(xlup).row range("a1:c1").select selection.copy range("a2:c" & lastrow).select selection.pastespecial paste:=xlpasteformulas, operation:=xlnone, _     skipblanks:=false, transpose:=false

it working, has stopped working. when run macro excel freezes. need press "esc" , macro stops, debugger says pastespecial wrong. not, if run macro import, , press "esc", imports well, run second macro paste formulas until last row , runs too.

the workbook in manual calculation

http://i.stack.imgur.com/hkzje.jpg

read csv file line line array 10,000 rows 100 columns. when array full write excel, redim array , continue until end of file.

i tried use dynamic array excel freeze up. using static array more efficient.

you'll need adjust constant values. image looks file semi-colon delimited. matter if columncount exact long greater or equal actual column count.

const csvfilename = "c:\users\best buy\downloads\stackoverfow\sample data file\r58k x c60.csv" const delimiter = "," const pagesize = 10000 const columncount = 100

it takes 32 seconds import 100,000 rows , 64 columns. csv file 69.5 mb on disk.

enter image description here

option explicit  sub processfile()     const csvfilename = "c:\users\best buy\downloads\stackoverfow\sample data file\r58k x c60.csv"     const delimiter = ","      activesheet.displaypagebreaks = false     application         .screenupdating = false         .calculation = xlcalculationmanual     end      debug.print     dim start: start = timer      dim lastrow long     dim arformulas     arformulas = range("a1:c1").formula      columns("a:bf").clearcontents      importcsvfile csvfilename, delimiter      debug.print "time import csv file in seconds:"; timer - start     start = timer      lastrow = cells(rows.count, "d").end(xlup).row      range("a1:c" & lastrow).formula = arformulas      debug.print "time add formulas in seconds:"; timer - start      debug.print "column count:"; worksheets("sheet1").usedrange.columns.count     debug.print "row count:"; worksheets("sheet1").usedrange.rows.count      application         .screenupdating = true         .calculation = xlcalculationautomatic         .autorecover.enabled = true     end end sub  sub importcsvfile(filepath string, delimiter string)     const pagesize = 10000     const columncount = 100      dim line string     dim ardata, arline     dim x long, y long, z long     redim ardata(pagesize, columncount)     z = 1      open filepath input #1                     ' open file input     while not eof(1)                               ' loop until end of file         line input #1, line         arline = split(line, delimiter)          y = 0         y = 0 ubound(arline)             ardata(x, y) = arline(y)         next         x = x + 1          if x = pagesize or eof(1)              range("d" & z).resize(x, y) = ardata             z = z + x             redim ardata(pagesize, columncount)             x = 0         end if      loop      close #1      erase ardata end sub

Comments

Post a Comment

Popular posts from this blog

mysql - Dreamhost PyCharm Django Python 3 Launching a Site -

-
i'm new python/django , created simple django website using pycharm. site works fine running on computer, i'm lost when comes taking site onto host (dreamhost). i'm able upload files correct directories fine, , believe made correct changes database on mysql. i'm not sure go there though. right now, page shows files rather site. direction appreciated. much! you should not run django application using python manage.py runserver on production. how run application on django depends on server (apache, nginx) want use for apache - https://docs.djangoproject.com/en/1.10/howto/deployment/wsgi/modwsgi/ for nginx - https://www.digitalocean.com/community/tutorials/how-to-serve-django-applications-with-uwsgi-and-nginx-on-ubuntu-14-04 the mysql database should, of course, exist , have correct credentials. or, if choose use sqlite, file should accessible, readable , writable www-user (may different depending on linux distributive)
Read more

java - Sending SMS with SMSLib and Web Services -

-
i have made rest web service send smss using hsdpa usb modem. using smslib in java send smss. every time web service invoked, create gateway start service, send message, stop service , remove gateway. takes 20 seconds each message. found starting service takes lot of time. part of code use send smss service.getinstance().addgateway(gateway); service.getinstance().startservice(); outboundmessage msg = new outboundmessage(phonenumber, message); if (service.getinstance().sendmessage(msg)) { result = "message sent successfully!!"; } else { result = "could not send message."; } service.getinstance().stopservice(); service.getinstance().removegateway(gateway);//remove gateway is there way can start service once if not started , use send message when ever web service invoked? why dont group messages , send in 1 go? service.getinstance().sendmessages(messa...
Read more

java - How to resolve The method toString() in the type Object is not applicable for the arguments (InputStream) -

-
i'm trying read json file local machine. @path("/") public class jsonparsing { file f = new file("file.json"); if (f.exists()){ inputstream = new fileinputstream("file.json"); string jsontxt = ioutils.tostring(is); system.out.println(jsontxt); jsonobject json = new jsonobject(jsontxt); string = json.getstring("1000"); system.out.println(a); } } but i'm getting error in tostring() method. possible read .txt file containing json object local machine? if possible, how done? firstly: whenever ask question- add imports well. secondly: yes possible read .txt file containing json object. steps: 1. add maven dependency in pom.xml - <dependency> <groupid>com.googlecode.json-simple</groupid> <artifactid>json-simple</artifactid> <version>1.1.1</version> </dependency> or add jar of dependency. 2.import...
Read more